select query get distinct records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select query get distinct records

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2008-07-16 : 14:39:10

when i execute the following query, i am getting multiple records of same data which is the combination of fields DRWID and Title, how to get distinct rows using DRWID & title

SELECT [DrwID]
,[ModuleRecordID]
,[ModuleName]
,[DrawingID]
,[SheetNO]
,[PageNO]
,[CurrentRevisionNO]
,[Title]
,[Attached]
FROM [TAB_ccsNetDrawingslog] where modulename='su' and modulerecordid=1

Thank you very much for the helpful info.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-16 : 14:55:33

do you need a single record for each DRWID + TITLE combination?
what if the ModuleName is different for different records having same DRWID and TITLE? which one do you want to pick

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-16 : 14:58:37

good question. Use the ORDER BY in the CROSS APPLY to control which row you want returned.

Here is one way:


SELECT t.[DrwID]
       ,t.[Title]
       ,ca.[ModuleRecordID]
       ,ca.[ModuleName]
       ,ca.[DrawingID]
       ,ca.[SheetNO]
       ,ca.[PageNO]
       ,ca.[CurrentRevisionNO]
       ,ca.[Attached]
FROM   [TAB_ccsNetDrawingslog] t
cross apply (
       select top 1 
              [ModuleRecordID]
              ,[ModuleName]
              ,[DrawingID]
              ,[SheetNO]
              ,[PageNO]
              ,[CurrentRevisionNO]
              ,[Attached]
       from   [TAB_ccsNetDrawingslog]
       where  [DrwID] = t.[DrwID]
       and    [title] = t.[title]
       order by [ModuleRecordID]
       ) ca
where  t.modulename='su' 
and    t.modulerecordid=1
group by t.[DrwID]
       ,t.[Title]
       ,ca.[ModuleRecordID]
       ,ca.[ModuleName]
       ,ca.[DrawingID]
       ,ca.[SheetNO]
       ,ca.[PageNO]
       ,ca.[CurrentRevisionNO]
       ,ca.[Attached]

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-16 : 15:02:49

i was confused about the question, am more confused with the solution TG provided.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-16 : 15:28:53

quote:
Originally posted by rohitkumar

i was confused about the question, am more confused with the solution TG provided.

based on your original question it seems like you understood the question the same way I did.

What specifically confuses you about my response?
Here is a little sample code that illustrates my solution if you want to play with some working code to see what it is doing:


declare @t table (DrwID int, title varchar(10), col1 int, col2 int)
insert @t 
select 1, 't1', 1, 1 union all
select 1, 't1', 2, 2 union all
select 2, 't2', 1, 1 union all
select 2, 't2', 2, 2

select t.DrwID
       ,t.title
       ,ca.col1
       ,ca.col2
from   @t t
cross apply (select top 1 col1, col2 from @t where drwid = t.drwid and title = t.title order by col1) ca
group by t.DrwID
       ,t.title
       ,ca.col1
       ,ca.col2

output:
DrwID       title      col1        col2
----------- ---------- ----------- -----------
1           t1         1           1
2           t2         1           1

Be One with the Optimizer
TG

reddymade
Posting Yak Master

165 Posts

Posted - 2008-07-16 : 15:32:06

Thank you very much TG. perfect answer.

quote:

Originally posted by TG

good question. Use the ORDER BY in the CROSS APPLY to control which row you want returned.

Here is one way:


SELECT t.[DrwID]
       ,t.[Title]
       ,ca.[ModuleRecordID]
       ,ca.[ModuleName]
       ,ca.[DrawingID]
       ,ca.[SheetNO]
       ,ca.[PageNO]
       ,ca.[CurrentRevisionNO]
       ,ca.[Attached]
FROM   [TAB_ccsNetDrawingslog] t
cross apply (
       select top 1 
              [ModuleRecordID]
              ,[ModuleName]
              ,[DrawingID]
              ,[SheetNO]
              ,[PageNO]
              ,[CurrentRevisionNO]
              ,[Attached]
       from   [TAB_ccsNetDrawingslog]
       where  [DrwID] = t.[DrwID]
       and    [title] = t.[title]
       order by [ModuleRecordID]
       ) ca
where  t.modulename='su' 
and    t.modulerecordid=1
group by t.[DrwID]
       ,t.[Title]
       ,ca.[ModuleRecordID]
       ,ca.[ModuleName]
       ,ca.[DrawingID]
       ,ca.[SheetNO]
       ,ca.[PageNO]
       ,ca.[CurrentRevisionNO]
       ,ca.[Attached]

Be One with the Optimizer
TG

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-16 : 15:38:05

declare @t table (DrwID int, title varchar(10), col1 int, col2 int)
insert @t
select 1, 't1', 1, 1 union all
select 1, 't1', 2, 2 union all
select 1, 't1', 3, 3 union all
select 2, 't2', 1, 1 union all
select 2, 't2', 2, 2

select t.DrwID
,t.title
,ca.col1
,ca.col2
from @t t
cross apply (select top 1 col1, col2 from @t where drwid = t.drwid and title = t.title order by col1 desc) ca
group by t.DrwID
,t.title
,ca.col1
,ca.col2

what if reddymade wants 1, t1, 2, 2, the solution you've given returns the extreme max or min of whatever you give in the order by clause.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-16 : 15:58:02

quote:
what if reddymade wants 1, t1, 2, 2, the solution you've given returns the extreme max or min of whatever you give in the order by clause.

Yes, that is why I said to you, "Good question". If there is any real answer as to rules for the specific row to be returned that doesn't conform to a standard ORDER BY then there are ways to achieve that. ie the rules could be defined in a CASE statement and ordered by that. The OP didn't provide any logic so I randomly ORDERed by the first column that wasn't in the logical key.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 16:19:03

And using ROW_NUMBER() function.

SELECT	DrwID,
	ModuleRecordID,
	ModuleName,
	DrawingID,
	SheetNO,
	PageNO,
	CurrentRevisionNO,
	Title,
	Attached
FROM	(
		SELECT	DrwID,
			ModuleRecordID,
			ModuleName,
			DrawingID,
			SheetNO,
			PageNO,
			CurrentRevisionNO,
			Title,
			Attached,
			ROW_NUMBER() OVER (PARTITION BY DrwID, Title ORDER BY ModuleRecordID) AS RecID
		FROM	TAB_ccsNetDrawingslog
		WHERE	ModuleName = 'su'
			AND ModuleRecordID = 1
	) AS d
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-16 : 17:02:58

quote:
Originally posted by TG

quote:
what if reddymade wants 1, t1, 2, 2, the solution you've given returns the extreme max or min of whatever you give in the order by clause.
Yes, that is why I said to you, "Good question". If there is any real answer as to rules for the specific row to be returned that doesn't conform to a standard ORDER BY then there are ways to achieve that. ie the rules could be defined in a CASE statement and ordered by that. The OP didn't provide any logic so I randomly ORDERed by the first column that wasn't in the logical key.

Be One with the Optimizer
TG

got it...and at the end reddymade found it working perfect for him, so we are good :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources