Select query using Top and distinct is it possible

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 2000 Forums

SQL Server Development (2000)

Select query using Top and distinct is it possible

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2005-11-28 : 13:12:47

Select query using Top and distinct both is it possible

I am using the following query, and the resultset contains duplicate records with same CNID. All i am trying to do is show me the last three records updated by me, using the datetime of update_dt field, i am getting the records from Log table, it will have the history of all the changes done for all records.

SELECT Top 3 CNID, Description,'CN' as Type,update_dt
FROM tab_ccsnetcn_log where updatedby = 'thisuser' ORDER BY update_dt DESC

I am getting the following result: it shows two record with same CNID: 149

CNID Description Type Update_dt
147 This isprocdatetest CN 2005-11-21 12:27:45.827
149 testtitleLagos CN 2005-11-21 11:48:02.670
149 testtitleLagos CN 2005-11-21 11:47:47.950

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-28 : 13:37:31

select t.CNID, t.Description,'CN' as Type,t.update_dt
from tab_ccsnetcn_log t
join (select top 3 CNID, update_dt = max(update_dt) from tab_ccsnetcn_log where updatedby = 'thisuser' group by CNID order by update_dt desc) t2
on t.CNID = t2.CNID
and t.update_dt = t2.update_dt

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

reddymade
Posting Yak Master

165 Posts

Posted - 2005-11-28 : 14:13:35

Hello NR,
Thank you very much, it works great.
Can you please tell me, I want to do a UNION with 5 select querys with same fields etc. but with different tables:

Can you please tell me how to use a Union with your solution.
this is the next query:
select t.RFID, t.Description,'RF' as Type,t.update_dt
from tab_ccsnetRF_log t
join (select top 3 RFID, update_dt = max(update_dt) from tab_ccsnetRF_log where updatedby = 'thisuser' group by RFID order by update_dt desc) t2
on t.RFID = t2.RFID
and t.update_dt = t2.update_dt

Thank you very much for the help.

quote:
Originally posted by nr

select t.CNID, t.Description,'CN' as Type,t.update_dt
from tab_ccsnetcn_log t
join (select top 3 CNID, update_dt = max(update_dt) from tab_ccsnetcn_log where updatedby = 'thisuser' group by CNID order by update_dt desc) t2
on t.CNID = t2.CNID
and t.update_dt = t2.update_dt

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-28 : 14:54:38

Reddymade,

UNIONS are very easy and the basic format is simply:

Select whatever you want
UNION
Select whatever else you want
UNION
Select whatever else you want

There are 2 types of UNIONS. The default is a DISTINCT type where it will combine all of the distinct values from all of your select clauses. This type causes a performance hit because it has to compare all of the result sets. The second type has to be identified by you which is UNION ALL and defines that you want all of the results from all of the selects and don't need to have any DISTINCT executed.

Select whatever you want
UNION ALL
select whatever else you want

You would simply run the exact query that they gave you, then put the words UNION ALL and then the complete query for the second table etc, through all 5 of your tables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-29 : 02:15:55

To your first question refer point 2 here also
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources