Tracker


		Advanced Search

HOME

TRACKER

MS SQL Server

SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.

How Can I Use SELECT DISTINCT And Maintain The Original Order

Say I have a result set with two fields numbers and letters.

1 A
3 A
1 B
2 B

The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order?
When I try
select distinct Number from MyResultSet

it will reorder the new result set by the Number field and return

1
2
3

However, I'd like maintain the Letter order and return

1
3
2

View Complete Forum Thread with Replies

Related Forum Messages:

How To Select Data From Excel Table By Original Column Order?
How Do I Use Order By When I Use Select Distinct.
SELECT DISTINCT W/ORDER BY
SELECT DISTINCT Type ORDER BY
SELECT DISTINCT MyId Order By MyDate
SELECT DISTINCT And ORDER BY With Aliased Column
How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
How To Maintain Transaction Order For Perpetual Inventory
How To Keep Original Order When Querying A Table?
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Order By Because Of Distinct
Distinct With Order By
SQL Distinct Value And Order By Some Field......
DISTINCT Requires ORDER BY
Distinct & Dynamic ORDER BY
Default Sort Order - Open Table - Select Without Order By
Extract Distinct Information And Order The Results
Specify Order For Select Results, Order By: Help!
INSERT-SELECT Depending On The Select:ed Order
Please Help Me: SQL SELECT DISTINCT
SELECT Distinct Help
Select Not Distinct?
SQL Select DISTINCT?
SELECT DISTINCT
Select Distinct Help?
Select Distinct
Select Distinct
Select Distinct
Select Distinct
Select Distinct
Select Distinct Help
Select Distinct
Select Distinct???
SELECT DISTINCT
Select Distinct
SELECT DISTINCT
Help With A Distinct Select?
Using Distinct And * In Select
Select Distinct
Select Distinct Records
Select Distinct Question
Select Distinct [name] From Merchantcategory
SELECT DISTINCT F1, F2, F3, F4 FROM 'table Name'
Select Distinct For Different Rows
Distinct In Select Statement
Select Distinct Rows
Very Slow Distinct Select
No Distinct In A Select Into Stement ?

How To Select Data From Excel Table By Original Column Order?
Hello, everyone:

I have an Excel table that has columns like,

Vendoe_Name
Level
Address
Email
Phone

I use the statemen

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]

and got output by column order,

Address
Email
Level
Phone
Vendoe_Name

How to make output by original column order like,

Vendoe_Name
Level
Address
Email
Phone

Any help will appreciated. Thanks

ZYT

View Replies !

How Do I Use Order By When I Use Select Distinct.
Hi
I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc
DECLARE @Counter TABLE(
PlanId int,
FundId int,
ClientFundName varchar(110),
DisplayOrder int IDENTITY(1,1),
IsDefault bit,
IsPortfolioFundOnly bit
)
INSERT INTO @Counter
(
PlanId,
FundId,
ClientFundName,
IsDefault,
IsPortfolioFundOnly
)
SELECT
5923,
f.FundId,
d.FundName,
CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END,
CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END
FROM
PlanDetail d
INNER JOIN Statements..Fund f
ON d.CUSIP = f.CUSIP
OR
d.Ticker = f.Ticker
OR
d.Ticker = f.ClientFundId
OR
d.CUSIP = f.ClientFundId
-- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether
--fund can only be chosen as part of a portfolio.
LEFT JOIN PlanDetail p
ON d.FundName = p.FundName
AND
d.PortfolioName = p.PortfolioName
WHERE
d.PlanNumber IS NOT NULL
AND
p.PortFundPercent IS NULL
GROUP BY
f.FundId,
d.FundName,
d.FundDefault,
--d.PlanNumber,
--d.Cusip,
-- d.Ticker,
--d.RowNumber,
p.FundType

ORDER BY
Min(d.PlanNumber),
Min(d.RowNumber)

any help will be appreciated.
Thanks
Karen

View Replies !

SELECT DISTINCT W/ORDER BY
I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT DISTINCT
pdm.Account,
pdm.Customer
FROM
dbo.Demands pdm LEFT OUTER JOIN
dbo.Tickets rct ON pdm.Account = rct.Account
WHERE
pdm.Code IN (66, 51)
ORDER BY
pdm.TransactionDate DESC

Is there any way to make the ORDER BY work in this case?

View Replies !

SELECT DISTINCT Type ORDER BY
I am trying to display items from a table that have a type. For each condition there are between 7 and 10 types. I am looping through each type and displaying all items in that type. I am using DISTINCT to pull the types and count them, so I know how many there are and how times to loop. My problem is that DISTINCT is ordering the types alphabetically! I want them in the same order they went into the table. I tried adding ORDER BY primaryID, but got an error that said I also had to select primaryID as well as type, so now I am selecting every item that fits the condition and not just the DISTINCT types! Is there any way to make it order by column_position? I am using SQL 2K.

View Replies !

SELECT DISTINCT MyId Order By MyDate
I have the following records,
MyDate MyId
2003/10/25 2
2003/10/26 3
2003/10/26 1
2003/10/27 3
2003/10/28 2
2003/10/29 4

I want to get the most earlier date distinct records.
I try to use "SELECT DISTINCT MyID from Table;"
I expect to get Myid: 2,3,1,4
But the computer returns Myid:1,2,3,4

Is there a way to get the records using
"SELECT DISTINCT MyID from Table ORDER BY MyDate;"

Appreciate help......

View Replies !

SELECT DISTINCT And ORDER BY With Aliased Column

This query demonstrates a problem I have run across:

USE AdventureWorks

GO

-- This query works fine.

SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query also works fine.

SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO

-- This query returns error 145

SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName

GO

The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".
It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it.
Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.

View Replies !

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View Replies !

How To Maintain Transaction Order For Perpetual Inventory
Hello

I would appreciate If any one could help me in this matter.

problem is : how to maintain perpetual inventory transaction table order in batch mode updation ?.

I have designed a table to hold all inventory transactions. The table order is perfectly maintained in online system of updation. But if I go with batch updation then the order of the transaction is collapsing. For example consider the following table design. (note I used auto number to maintain the order).

version used : SQL Server 2000 with service pack updates.

LINEID TRANS_TYPE TRANS_ID TRAN_DATE STOCKNO QUANTITY RATE

1 Receipts 2 01/01/2000 xxxxx1 2 2.0
2. Issue 1 01/01/2000 yyyyy1 1
3 Receipts 3 02/01/2000 xxxxx2 4 3.0
4 Issue 2 02/01/2000 yyyyy2 1

the above is well in online mode. But in batch mode if i update issue 2 before receipts 3 then i am getting order like below

1 Receipts 2 01/01/2000 xxxxx1 2 2.0
2. Issue 1 01/01/2000 yyyyy1 1
3 Issue 2 02/01/2000 yyyyy2 1
4 Receipts 3 02/01/2000 xxxxx2 4 3.0

ofcourse if the order collapse means costing can not be accurate. so please any one could help me to solve this problem. because many software packages are not posting in sequence if we choose in batch mode.

View Replies !

How To Keep Original Order When Querying A Table?
Hello, everyone:

I have a table like:

ColName
b
b
b
d
d
d
a
a
c
c
c

I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c

However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d

Can any one have the idea to handle that? Thanks

ZYT

View Replies !

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache

When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."

Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View Replies !

Order By Because Of Distinct
I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query:

select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2753
100 2753
100 2071
100 2753

Now I change the query to:

select distinct ofd_fk_off_id, ofd_fk_class_id from
(select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers

This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2071
100 2753

In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.

View Replies !

Distinct With Order By
It gives me this error:
ORDER BY clause (TimeStamp) conflicts with Distinct

The sql statement is:

sql Code:

Original
- sql Code

SELECT DISTINCT division from table order by Stamp DESC

SELECT DISTINCT division FROM TABLE ORDER BY Stamp DESC

I've tried:

sql Code:

Original
- sql Code

SELECT DISTINCT division, Stamp FROM table ORDER BY Stamp DESC

SELECT DISTINCT division, Stamp FROM TABLE ORDER BY Stamp DESC

But this produces duplicate rows, which in this specific case is unacceptable.

Is there any way I can acheive what I want?
Stamp is a TimeStamp if you couldn't figure it out. I just want the last one entered, but all the last ones entered per division. . .

View Replies !

SQL Distinct Value And Order By Some Field......
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan

View Replies !

DISTINCT Requires ORDER BY
OK I am trying the following select statement but I am getting a 'DISTINCT requires ORDER BY to be used' error. I have an ORDER BY in it so I am not sure what I have missed?

SELECT DISTINCT tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum AS [Full Cost]
FROM tbl_final_CP INNER JOIN
ContractorAreaRelationship ON tbl_final_CP.Area = ContractorAreaRelationship.AreaNo INNER JOIN
tbl_CPCost ON tbl_final_CP.CP = tbl_CPCost.CP
WHERE (DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND
(DATENAME(mm, tbl_final_CP.dCount) = 'Conwy') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (ContractorAreaRelationship.ContractorNumber = 6112) AND
(DATENAME(mm, tbl_final_CP.dCount) = 'March') AND (ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND
(ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.Video > 0) AND (ContractorAreaRelationship.AdminID = 6112) OR
(DATEPART(yyyy, tbl_final_CP.dCount) = DATEPART(yyyy, GETDATE())) AND (DATENAME(mm, tbl_final_CP.dCount) = 'March') AND
(ContractorAreaRelationship.AreaName = 'Conwy') AND (tbl_final_CP.ExtEnum > 0) AND (ContractorAreaRelationship.AdminID = 6112)
ORDER BY tbl_final_CP.CP, tbl_final_CP.ExtEnum + tbl_final_CP.ExtEnum

View Replies !

Distinct & Dynamic ORDER BY
Hello,

I have a stored procedure with dynamic ORDER BY. I would like to use the DISTINCT too. Is it somehow possible?
Thank you

Here is the stored procedure:

SELECT identifier_company + cast(identifier_number as nvarchar(3)) as identifier,
CASE WHEN canceled = 'True' THEN 'canceledPO' ELSE '' END AS style,
staff.staff_name,
purchase.purchase_id,
purchase.traveller_name,
nominal_department.department_name,
purchase.canceled,
purchase.travel_date,
convert(nvarchar(20), purchase.date_raised, 103) as dated,
supplier
FROM purchase INNER JOIN purchase_project ON purchase.purchase_id = purchase_project.purchase_id
INNER JOIN staff ON purchase.raised = staff.staff_id
INNER JOIN nominal_department ON purchase.department = nominal_department.nominal_dep_id
WHERE (raised in (SELECT staff_id FROM staff WHERE department like @FromDepartment) or purchase.raised = @raisedBy)
and purchase_project.project_number like '%' + @Query + '%'
ORDER BY
CASE @SortDir
WHEN 'ASC' THEN
CASE @OrderBy
WHEN 'staff_name' THEN cast(staff_name as nvarchar(100))
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'department_name' THEN cast(department_name as nvarchar(100))
WHEN 'supplier' THEN cast(supplier as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(10))
END
END
ASC,
CASE @SortDir
WHEN 'DESC' THEN
CASE @OrderBy
WHEN 'staff_name' THEN cast(staff_name as nvarchar(100))
WHEN 'traveller_name' THEN cast(traveller_name as nvarchar(100))
WHEN 'department_name' THEN cast(department_name as nvarchar(100))
WHEN 'supplier' THEN cast(supplier as nvarchar(100))
WHEN 'canceled' THEN cast(canceled as nvarchar(10))
END
END
DESC

View Replies !

Default Sort Order - Open Table - Select Without Order By
Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View Replies !

Extract Distinct Information And Order The Results
Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.

I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description
5111     12345        420          reversal
5112     12345        200          auths
5113     15236        200          auths
5114     46587        200          auths
5115     46587        420          reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.

SELECT * FROM SAMPLE
WHERE ret_ref_no in
(
    SELECT ret_ref_no FROM SAMPLE
    GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query
5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards
Deceptive

View Replies !

Specify Order For Select Results, Order By: Help!
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View Replies !

INSERT-SELECT Depending On The Select:ed Order
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).

View Replies !

Please Help Me: SQL SELECT DISTINCT
I have a table myTable (ID, Year, Name, Note)data in this table:ID Year Name Note 1 2008 Petter hdjhs2 2008 Nute jfdkfd3 2007 Suna dkfdkf4 2007 Para jfdfjd5 2009 Ute dfdlkf Please help me to Select DISTINCT [Year]]ex:1 2008 Petter hdfdfd3 2007 Suna fdkfdk5 2009 Ute fkdfkdfd Thank!

View Replies !

SELECT Distinct Help
Hello Everyone
Hopefully someone can help me create a SQL statement for this.
I need the ff: fields
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE, Specialty
Let say I have a table.
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine1000 998 Mike James Allen 75021 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist3333 701 John Smith Dallas 72002 Dermatologist2222 630 Terry Walker Frisco 75001 Optalmologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 454 Tim Johnson San Anontio 72500 Internal Medicine 4444 464 Tim Johnson Frisco 72660 Internal Medicine
I want to select only "one" instance of the provider it doesnt matter what is selected
either the first address or the second address.
It should show
Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 464 Tim Johnson Frisco 72660 Internal Medicine
And yes, the table is not Normalized..Is there anyway I could get away with it without having to normalize?
Thanks
Lorenz

View Replies !

Select Not Distinct?
Is their a way to select all items from a table that are not distinct? Meaning, I want to know which items in a column occur more than once.
Example:
Suppose we have a table with student names, ss# and address. I want to display only records where their is more than one studen with the same name. So for example their could be ten people with the name of "Mike" in a class?

Ralph

View Replies !

SQL Select DISTINCT?
OK I have a Forum on my website make up of 3 tablesTopisThreadsMessageI show a list of the 10 most recent Changed Threads. My Problem is that my Subject field is in the messages Table, IF I link Threads to Messages then try to use Select Disticnt I get mutliple Subject fields as the messsges are not unique (obvisally) So I want to get the top 10 Threads by postdate and link to the Messages table to get the Subject headerAny help? Or questions to explain it better?

View Replies !

SELECT DISTINCT
I don't know what the correct syntax is to do what I want with the DISTINCTfunction (if it's actually possible).I have a query which displays a variety of fields from a variety of tables(pretty standard).However, I only want to show records where the contents of one particularcolumn in the query are unique - I do not want to perform the function onthe entire record because other fields in the records may be duplicated foras reason.

View Replies !

Select Distinct Help?
Can you have "Select Distinct" in Union Query,because that is what I am trying to do and this is the error message I get.

"The text, ntext, or image data type cannot be selected as DISTINCT."

I would need to do that because i have duplicate records,because these records are getting written into the db when templates are generated and sometimes if they double click it generates two and writes that many results as well, so that is why I was thinking that select distinct would solve my problem.

Thanks for your help

This is the query in question:

SELECT Distinct 'O' AS Origin, a.RecordID, a.RelocateID, a.SupplierID, a.DateIn, a.DateOut, a.NoOfDays, a.AgreeAmt, a.PaymentMethod, a.AccomType, a.Reason,
a.InvRecvd, a.RelocateeTempAccomTS, a.BedConfiguration, a.NumberOfPax, a.AdditionalItems, a.Currency, a.TotalAmount, a.EnteredBy,
a.LastModifiedBy, a.ReferenceNumber, a.Location, a.Comments, a.ArrivalTime, a.PONumber,CommissionRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates c
WHERE a.Currency = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates c
WHERE 'AUD' = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS AUDExchangeRate, a.WhenConfirmed, e.RequestID AS RequestID,
e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause,
dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc
FROM dbo.RelocateeTempAccom a LEFT OUTER JOIN
dbo.luDecisionMaker ON a.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT OUTER JOIN
dbo.viewZYesno ON a.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN
dbo.docRequests e ON '{RelocateeTempAccom.RecordID}=' + CONVERT(VARCHAR a.RecordID) = e.WhereClause
WHERE (ISNULL(a.Cancelled, 0) = 0)

UNION ALL

SELECT Distinct 'D' AS Origin, RecordID, RelocateID, DTASupplierID AS SupplierID, DTADateIn AS DateIn, DTADateOut AS DateOut, DTANoOfDays AS NoOfDays,
DTAAgreeAmt AS AgreeAmt, DTAPaymentMethod AS PaymentMethod, DTAAccomType AS AccomType, Reason, InvRecvd,
RelocateeDTATS AS RelocateeTempAccomTS, BedConfiguration, NumberOfPax, AdditionalItems, Currency, DailyTotal AS TotalAmount, EnteredBy,
LastModifiedBy, ReferenceNumber, Location, Comments, ArrivalTime, PONumber,CommissionRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates d
WHERE b.Currency = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL
((SELECT TOP 1 ExchangeRateToUSD
FROM luCurrencyExchangeRates d
WHERE 'AUD' = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS AUDExchangeRate, WhenConfirmed, e.RequestID AS RequestID,
e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause,
dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc
FROM dbo.RelocateeDTA b LEFT JOIN
dbo.luDecisionMaker ON b.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT JOIN
dbo.viewZYesno ON b.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN
dbo.docRequests e ON '{RelocateeDTA.RecordID}=' + CONVERT(VARCHAR, b.RecordID) = e.WhereClause
WHERE ISNULL(Cancelled, 0) = 0

View Replies !

Select Distinct
Hi,

I wonder if anyone here can shed some light on why the query below produces duplicate EmailAddress values even though we specify the DISTINCT clause.

SELECT DISTINCT(EmailAddress) SubscriberID, FirstName, Surname, SubscriberID
FROM TestMailingList
ORDER BY EmailAddress

Thanks.

View Replies !

Select Distinct
Hi! I have 4 tables and they have a common column (eg. regionid). These
4 tables have data overlapping with the others. Some data exist in a table
but not on the others. What I want to do is to do a select that will display
all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others.

Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource.

Any tips, hints or info will be appreciated.
thanks in advance.

zrxowm

Table REGION1 :
RegionID RegionDescription
----------- --------------------------------------------------
10 Place1
11 Place11
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION2 :
RegionID RegionDescription
----------- --------------------------------------------------
21 Place21
22 Place22
1 Eastern
2 Western
3 Northern
4 Southern
(6 row(s) affected)

Table REGION3 :
RegionID RegionDescription
----------- --------------------------------------------------
33 Place33
31 Place31
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

Table REGION4 :
RegionID RegionDescription
----------- --------------------------------------------------
41 Place41
42 Place42
1 Eastern
2 Western
3 Northern
4 Southern

(6 row(s) affected)

View Replies !

Select Distinct
Can I run Select distinct on one fieldname only while I'm selecting more than one fielname, like

Select Distinct col1, col2, col3 from table

I need distinct on col1 only and not on the other 2 columns, is it possible.

Thanks

View Replies !

Select Distinct
Does anyone know why this does not work?

SELECT DISTINCT tb2.column20 tb2.column20, tb1.column10, tb2.column21, tb2.column22, tb3.column30
FROM table1 tb1, table2 tb2, table3 tb3
WHERE tb1.column11 = 'P'
AND tb2.column23 = 'P'
AND tb1.column12 = tb2.column24
AND tb2.column25 = tb3.column31
ORDER BY tb2.column20

Its supposed to return only the distinct entries in tb2.column20

View Replies !

Select Distinct
I may be new at this but I can't find any explanation why
SELECT DISTINCT(Stno), Grade
shows distinct occurrences for each Stno-Grade combination rather than just distinct occurences of Stno. What is the solution?

View Replies !

Select Distinct Help

select distinct ISNULL (a.account,'') as "Account", ISNULL (c.address1,'') as "Address",
ISNULL (c.city,'') as "City", ISNULL (c.state,'') as "State",
ISNULL (c.postalcode,'') as "Zip Code", ISNULL (a.mainphone,'') as "Phone",
a.userfield1 as "GID", s.division
from sysdba.account as a
join sysdba.address as c on a.addressid = c.addressid
join sysdba.staff as s on a.accountid = s.accountid
where a.type like '%client%' and a.userfield1 is not null and (s.division like '%HR%' or s.division like '%db%') and s.type = 'client'

So what happens now is that if an account is listed in two division I get two distinct rows returned, but each with the same GID column. When I try to push this to a new database that has GID as the primary key I get duplicate on that column and it errors out.

I need to be able to get only a single row if the division is both HR and db. how to tackle this problem.

Thanks!

View Replies !

Select Distinct
Hi members,

Is there a way to count the number of data with distinct column a and column b (combination)??

ex

col A               Col B
1                        1
1                        2
1                        1
2                        1
3                        3
3                        3
4                        3

should give 5.
The ones in red are duplicates that I want to eliminate.
Thanks,

View Replies !

Select Distinct???

select ExpenseCodeID, [Group], SubGroup, GLAccount,ExpenseCode, ProjType

from BridgeFinance..OPS_ExpenseCodes

Order By ExpenseCode

I have this query only thing wrong with it is that I dont know how to only select different values from my expenseCode column that looks like below....I dont want to select "Employee Only Meals" as many times as it appears in the table just once do i want to select it....any help with how i should write my query would be great! thanks!

Administrative contract work
Cell phone
Courier/Shipping
Employee only Meals
Employee only Meals
Employee only Meals
Employee only Meals
Employee only Meals
Employee trans/parking
Health Club Memberships
Home Office Expenses
IT equipment-non capitalizable

View Replies !

Select Distinct
I have a select query
Select distinct a,b,c,d from xyz
I would like to know what the syntax is if I want only a,b,c to be distinct and not d.
I tried something like
Select (distinct a,b,c),d
but getting error what is the correct query to do this.
Please help.

View Replies !

SELECT DISTINCT
Hello,

When I try the SELECT DISTINCT like this:

USE CHEC

SELECT DISTINCT
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
DAT01.[_@550] AS LoanType,
DAT01.[_@051] AS Branch,
DAT01.[_@TP] AS ProdTypeDescr,
SMT_Branches.[BranchTranType] AS TranType,
--SMT_Branches.[AUCode] AS AuCode,
COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
ON DAT01.[_@040] = [_@040]
INNER JOIN SMT_BRANCHES
ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
AND DAT01.[_@051] = '540'
--And SMT_Branches.[AUCode] = '1882'
And DAT01.[_@TP] = '115'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
SMT_Branches.[BranchTranType],
--SMT_Branches.[AUCode],
[DATE_CONVERSION_TABLE_NEW].MONTH,
DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
DAT01.[_@051],
DayofMonth ASC
--SMT_Branches.[AUCode] ASC
--COMPUTE sum(count(*))

I get the same result set as before.

What do I need to change?

Kurt

View Replies !

Help With A Distinct Select?
Hi,

I am new to this forum so hello to everyone!

I need some help getting unique records from a query, I have a large amount of nested selects and i want to only display distinct records, I have a unique identifier (party ID) but the code was written by someone else (who is on holiday!) and i need to work out where to insert the disctinct select (if at all? - open to a better way?) this query should pull back records and then the results are pasted in to excel, however would a DTS solve the issue with duplicates??

Any help more than appreciated!

Heres the code...

CREATE PROCEDURE dbo.negative_surplus_report

AS

SELECT dbo.Cubit_Override_ID.UserName AS [User], dbo.Cubit_Customers.RecordDateTime AS Date,

dbo.Cubit_Customers.Customer_Status AS [Customer Status], dbo.Cubit_Customers.Call_Prompted_By AS [Call Prompted By],

dbo.Cubit_Outcomes.Outcome_Description AS [Outcome], ISNULL(dbo.Cubit_EPH.Total_Balance, 0) AS [Egg Debt], ISNULL(dbo.Cubit_Debt.Income_Total,

0) AS Income, ISNULL

((SELECT SUM(Balance)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Card Debt], ISNULL

((SELECT SUM(Balance)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Loan Debt], ISNULL(dbo.Cubit_Spending.Out_Mortgage, 0)

AS [Mortgage Payment], ISNULL(dbo.Cubit_Spending.Out_Rent, 0) AS [Rent Payment], ISNULL(dbo.Cubit_Debt.Mortgage_Balance, 0)

AS [Mortgage Balance], ISNULL(dbo.Cubit_Debt.Property_Value, 0) AS Property, ISNULL(dbo.Cubit_Customers.Party_ID, '') AS [Party ID],

ISNULL(dbo.Cubit_Customers.Cubit_Cust_ID, '') AS [Cubit ID], ISNULL(dbo.Cubit_Spending.Out_Total, 0) AS Outgoings,

ISNULL(dbo.Cubit_EPH.Total_Monthly_Pmt, 0) AS [Egg Payments], ISNULL

((SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Card Paymements], ISNULL

((SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Loan Payments], dbo.Cubit_Debt.Income_Total -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - dbo.Cubit_Spending.Out_Total - dbo.Cubit_EPH.Total_Monthly_Pmt AS Surplus,

dbo.Cubit_Override_ID.Mandate_Level

FROM dbo.Cubit_Customers INNER JOIN

dbo.Cubit_Managers ON dbo.Cubit_Customers.Manager_ID = dbo.Cubit_Managers.Manager_ID INNER JOIN

dbo.Cubit_Areas ON dbo.Cubit_Managers.Area_ID = dbo.Cubit_Areas.Area_ID LEFT OUTER JOIN

dbo.Cubit_EPH ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_EPH.Cust_ID LEFT OUTER JOIN

dbo.Cubit_Spending ON dbo.Cubit_Spending.Cust_ID = dbo.Cubit_Customers.Cubit_Cust_ID INNER JOIN

dbo.Cubit_Outcomes ON dbo.Cubit_Customers.Outcome_ID = dbo.Cubit_Outcomes.Outcome_ID LEFT OUTER JOIN

dbo.Cubit_Additional_MI_Data ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_Additional_MI_Data.Cubit_Cust_ID INNER JOIN

dbo.Cubit_Override_ID ON dbo.Cubit_Customers.Input_By_NTID = dbo.Cubit_Override_ID.NT_ID LEFT OUTER JOIN

dbo.Cubit_Debt ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_Debt.Cust_ID

WHERE (dbo.Cubit_Areas.Area_ID IN (2, 3, 4, 11, 12)) AND (dbo.Cubit_Customers.Non_Relevant_Call = 0) AND (dbo.Cubit_Customers.Spending_Assessed = 1)

AND (dbo.Cubit_Customers.Debt_Assessed = 1) AND (dbo.Cubit_Debt.Income_Total > 0) AND (dbo.Cubit_EPH.Total_Monthly_Pmt < 999999) AND

(dbo.Cubit_Debt.Income_Total -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Card INNER JOIN

Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) -

(SELECT SUM(Monthly_Pmt)

FROM Cubit_Debt_Loan INNER JOIN

Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID

WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - dbo.Cubit_Spending.Out_Total - dbo.Cubit_EPH.Total_Monthly_Pmt < 0) AND

(dbo.Cubit_Customers.RecordDateTime >= '04/11/2006')

ORDER BY dbo.Cubit_Areas.Area_ID, dbo.Cubit_Override_ID.UserName, dbo.Cubit_Customers.RecordDateTime, Cubit_Customers.Cubit_Cust_ID

Thanks!

Matt

SQL newbie!

View Replies !

Using Distinct And * In Select
Bahrudeen writes "Hi..
hw to use Select query for both distinct and *

(eg) select * , distinct(building_id) from g_building where
(condition)

i want all information with distinct building id..
give a solution

advance thanx..."

View Replies !

Select Distinct
Hi. I am trying to create a view where it will find out the sum of hours for each employee, for each month and year.

SELECT DISTINCT EmpId,
SUM(Hours) AS Hours,
YEAR(WeekStartDate) AS startyear,
MONTH(WeekStartDate) AS startmonth
FROM dbo.BankHours_History
GROUP BY EmpId, WeekStartDate

View Replies !

Select Distinct Records
Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel

View Replies !

Select Distinct Question
Is there a way to do a Select Distinct on a single column in a result set?
Example:
Select Distinct(PersonID) PersonID, FirstName, LastName From People

View Replies !

Select Distinct [name] From Merchantcategory
hi all,
i wanna ask a question regarding select distinct statement.....
this is my sql query : select distinct [name] from merchantcategory
how can i get the other attribute from the result of the query ???
is this possible ?
thanks

View Replies !

SELECT DISTINCT F1, F2, F3, F4 FROM 'table Name'
Newbie question
SELECT DISTINCT F1, F2, F3, F4 FROM 'table name' returns distinct rows for whole table. Is there a way to just return distinct rows from say column F1 instead of all the fields. I suppose i could just do SELECT DISTINCT F1, but also would like to display other fields. Thanks in advance

View Replies !

Select Distinct For Different Rows
I have the following tablecolumns: [col1], [col2],[col3] and [NAME].I want to select the name column for each row where [col1]='07'.The problem is that there are several rows where [col1] contains '07' and also the name is the same. [col2] and [col3] contain different data for these double rows...however, I cant use the [col1] and [col2] values in my query because I dont know what values they contain beforehand.So now, when I execute my query and add the DISTINCT key I still get all the double rows!I hope this explains my problem, help is really appreciated...ow, btw: deleting the double rows is not an option....

View Replies !

Distinct In Select Statement
Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if Iâ€™m searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, Iâ€™m just planning ahead.

Iâ€™m programming using VB so I guess I would do something like this.

State = Trim(Request.QueryString("State"))

SelectStatement = "Select * From Products Where State='" & _
State & "'"

This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.

View Replies !

Select Distinct Rows
Hi,

I'm having a little bit of trouble trying to figure out how to do this query, right now I have:

SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId
FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU
WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null

and it returns something like this:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
1665 -30987 SELECT_OOS11
2433 -30987 SELECT_OOS11
4272 -30984 SELECT11
2436 -30984 SELECT11
2793 -32708 SELECT11

But I only it want it to return 1 record for each ProductID like so:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
4272 -30984 SELECT11
2793 -32708 SELECT11

ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this?

Thanks

View Replies !

Very Slow Distinct Select
My table looks like this:char(150) HTTP_REF,char(250) HTTP_USER,char(150) REMOTE_ADDR,char(150) REMOTE_HOST,char(150) URL,smalldatetime TIME_STAMPThere are no indexes on this table and there are only 293,658 records total.When I do a select like this it takes forever:SELECT COUNT(DISTINCT REMOTE_ADDR)Takes 2 minutes. Is there anyway to speed that up?Thanks

View Replies !

No Distinct In A Select Into Stement ?
Dear MSSQL experts,I use MSSQL 2000 and encountered a strange problem wqhile I tried touse a select into statement .If I perform the command command below I get only one dataset which hasthe described properties.If I use the same statement in a select into statement (see the secondselect) I get several datasets with the described properties like Ididn't use distinctIs there any posiibility to use destinct in a select into statementselect distinct IDENTITY (int) as ID, Title1 as Title1, Title2 asTitle2, Title3 as Title3,AggregationTitle1 as AggregationTitle1, AggregationTitle2 asAggregationTitle2,AggregationTitle3 as AggregationTitle3, AggregationTitle4 asAggregationTitle4from Variables where Title1 is not NULL or Title2 is not NULL orTitle3 is not NULL orAggregationTitle1 is not NULL or AggregationTitle2 is not NULL orAggregationTitle3 is not NULL or AggregationTitle4 is not NULL;This is the same with select into :select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 asTitle2, Title3 as Title3,AggregationTitle1 as AggregationTitle1, AggregationTitle2 asAggregationTitle2,AggregationTitle3 as AggregationTitle3, AggregationTitle4 asAggregationTitle4into VarTitles from Variables where Title1 is not NULL or Title2 isnot NULL or Title3 is not NULL orAggregationTitle1 is not NULL or AggregationTitle2 is not NULL orAggregationTitle3 is not NULL orAggregationTitle4 is not NULL;Hope anyone can help.Best regards,Daniel WetzlerI

View Replies !