Select Distinct Records in Sql Server

[opspl_programme]

Hi,

I have a dynamically generated sql query that is generated in VB6 and then passed on to SQL Server 2005. The query joins(inner join, left join, etc.) multiple tables.

If I were to execute that query in Sql Server Management Studio, I would get something like this:

TrayID| TrayID | EVA_ID
212 | 212 | NULL
313 | 313 | NULL
852 | 852 | 456-89
759 | 759 | 654-22

The duplication for the field i.e. 'TrayID' is by intention.
**How to fetch the distinct 'EVA_ID'? **

My initial idea was to insert these into a temporary (ex. #TempTable) table and then select distinct for 'EVA_ID'. But this fails because there is a duplicate 'TrayID'

Thanks.

[BitBlt]

Your request makes no sense. If you wanted to select for distinct EVA_ID you'd just write "select distinct EVA_ID from myTable" and be done with it. TrayID wouldn't even come into the mix, and any duplicates get collapsed into a single line.

There seems to be a bit more constraining your scenario than you've told us. Can you provide a little more detail?

[opspl_programme]

Hi,

The VB6 query is generated dynamically. The user selects a field for which distinct records are required. It needs to be distinct from amongst the results only.

In this case it is 'EVA_ID'. But it might be a field other than 'EVA_ID' also.

The query is listed below(Screenshot also included):

Select locations.locationid, LocationName, LocCol, LocRow , A.*, B.*, C.*, D.*, E.*, F.*, G.*, H.* from dbo.locations  
left join (select 0 as [Collection_ValueID], CollectionID, CollectionName from collections) as A 
on locations.fkCollectionID=A.collectionid 
left join (select 0 as [List Number_ValueID], fkLocationID, fkListID from ListItems) as B on locations.LocationID=B.fkLocationID 
left join (select 0 as [List Name_ValueID], ListId, ListName from Lists)  as C on C.ListId = B.fkListID 
INNER JOIN (Select 0 as [L1 Storage_ValueID], trayID, FacilityName 
from dbo.vwTankStackTray where FacilityName  like '%a%') as D on locations.fktrayid=D.trayid 
left JOIN (Select 0 as [L2 Storage_ValueID], trayID, TankName from dbo.vwTankStackTray) as E on locations.fktrayid=E.trayid 
left JOIN (Select 0 as [L3 Storage_ValueID], trayID, StackName from dbo.vwTankStackTray) as F on locations.fktrayid=F.trayid 
left JOIN (Select 0 as [L4 Storage_ValueID], trayID, TrayName from dbo.vwTankStackTray) as G on locations.fktrayid=G.trayid 
left join (Select EPLocValueID as [EVA_ID _ValueID], fkLocationID, EPLocValueText as [EVA_ID ] 
from vwEPSearchSubFields where EPName='EVA_ID ') as H on Locations.LocationID=H.fklocationid where isarchived='False' 
order by locationid

[urtrivedi]

I am not sure about your query, but If you want distinct row, if you add distinct keyword after select keyword you will distinct result
so if you say

select distinct TrayID, TrayID , EVA_ID from tablename

The you will get following result

TrayID| TrayID | EVA_ID
313 | 313 | NULL
852 | 852 | 456-89
759 | 759 | 654-22

[opspl_programme]

Thanks urtrivedi for the help...

I need 'EVA_ID' from amongst the results only. Perhaps if I explain a bit of my VB6 code, you will get a background idea of the project... The user selects various search criteria. This is then executed; the generated results are used to populate a grid.

In the next stage, the user will then select which field to pick distinct records from. Further the user will select a number (say 2). So for ex. the user selects 'EVA_ID' and 2. The end result needs to be such that I will take the first distinct record, assume it's 'EVA_ID' with the value '456-89'.

I will loop through the entire grid where 'EVA_ID' = '456-89'. Since the user has selected 2, I will tick/check mark the first record and then the second one(if it exists). I exit the loop(in this case) once the second record is ticked or end of the grid is reached.
The loop is then repeated to tick/check the next 2 records for the next distinct 'EVA_ID'.

[opspl_programme]

I modified the query to match the format below::-

Select EVA_ID from <Kept As Is> <Removed 'order by locationid'> <group by EVA_ID>.

One may add 'WHERE EVA_ID IS NOT NULL'

Here's the query for someone who might be interested
Select EVA_ID from dbo.locations
left join (select 0 as [Collection_ValueID], CollectionID, CollectionName from collections) as A
on locations.fkCollectionID=A.collectionid
left join (select 0 as [List Number_ValueID], fkLocationID, fkListID from ListItems) as B on locations.LocationID=B.fkLocationID
left join (select 0 as [List Name_ValueID], ListId, ListName from Lists) as C on C.ListId = B.fkListID
INNER JOIN (Select 0 as [L1 Storage_ValueID], trayID, FacilityName
from dbo.vwTankStackTray where FacilityName like '%a%') as D on locations.fktrayid=D.trayid
left JOIN (Select 0 as [L2 Storage_ValueID], trayID, TankName from dbo.vwTankStackTray) as E on locations.fktrayid=E.trayid
left JOIN (Select 0 as [L3 Storage_ValueID], trayID, StackName from dbo.vwTankStackTray) as F on locations.fktrayid=F.trayid
left JOIN (Select 0 as [L4 Storage_ValueID], trayID, TrayName from dbo.vwTankStackTray) as G on locations.fktrayid=G.trayid
left join (Select EPLocValueID as [EVA_ID _ValueID], fkLocationID, EPLocValueText as [EVA_ID ]
from vwEPSearchSubFields where EPName='EVA_ID ') as H on Locations.LocationID=H.fklocationid where isarchived='False'
group by EVA_ID