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.
| Author |
Topic |
|
Telek
Starting Member
2 Posts |
 Posted - 2009-03-03 : 11:27:03
|
| Ok, after beating my head for a day there has GOT to be an easy way of doing this. MySQL can do it in about a dozen different ways, but MSSQL seems to not be able to.I have an aggregate query which piles through a bunch of databases and returns a result set like so (simplified):entry_id,group_id,cost 1001 2001 5 1001 2001 4 1001 2000 5 1002 2001 3 1002 2000 2now all I need to do is skip the second row so that I can use the cost to calculate something based on transactions. Very simple! Basically generate distinct results on entry_id and group_id but not on cost. Can't figure it out.I've tried distinct, but it works on all rows. This is a simplified result set, so using min/max as aggregates to get around the distinct limitation won't work because it won't choose an entire row. I've tried creating a temporary table with unique constraint, but can't find a way to get it to just insert the rows that don't fail. There doesn't appear to be a first aggreate function. I've tried doing a where not exists select but because of how complicated the initial query is it becomes a huge mess.All I need to do is return the result set with distinct entry_id,group_id columns by choosing only the first result that matches in the result set.There has got to be a way to do this, right?Of course, to make matters worse, I have to use SQL Server 2000.Thanks in advance for your assistance! |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-03 : 11:50:50
|
| [code]select distinct entry_id,group_idfrom...[/code]No ? |
 |
|
|
Telek
Starting Member
2 Posts |
Posted - 2009-03-03 : 12:17:00
|
| No, because I need the cost data and thus must include that in the distinct which turns the entire row distinct and returns all 5 rows.I've found a solution using partition over and row_count() which is interesting and simple but I'm still wonderdering what other options there are. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-03-03 : 12:23:35
|
Offcourse, There can be other solutions. Also partition over and row_count() won't work in sql 2000. Which row would you want to return in this case :entry_id group_id cost1001 2001 51001 2001 4 You'd need to define a criteria. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-03 : 12:25:27
|
| there's no concept of first and last unless you specify order by means of another column. so for each entry_id,group_id group, which record does you want in output?what determines the record to be chosen? |
|
|
|
|
|
|
|
|
Select distinct on multiple columns