Returning max column and another at same time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Returning max column and another at same time?

Author

Topic

Tigerite
Starting Member

30 Posts

Posted - 2003-06-23 : 10:30:55

Hi, I've a table (R_TABLE) which is:


R_PK | C_PK | NUM

3794 | 1506 | 1.46
3794 | 1507 | 1.32
3795 | 1507 | 1.02
3795 | 1509 | 1.44
3795 | 1512 | 1.62
3796 | 1516 | 0.95
3796 | 1517 | 0.94
3796 | 1518 | 0.94

What I'd like is a way of doing a group by on R_PK to get the max NUM but also to return the
corresponding C_PK with it. However, the one catch is that I could have the same NUM with
two C_PKs and in this case I only want to return either one (it doesn't matter which).
So in this case, the result set should be:


R_PK | C_PK | MAX_NUM

3794 | 1506 | 1.46
3795 | 1512 | 1.62
3796 | 1518 | 0.94

The best I could come up with was


select R.R_PK, R.C_PK, RM.MAX_NUM
  from (select R_PK, MAX(NUM) MAX_NUM
          from R_TABLE
         group by R_PK) RM
 inner join R_TABLE R
    on R.R_PK = RM.R_PK
   and R.NUM = RM.MAX_NUM

But, obviously it doesn't work as I get duplicates in the 3794 row:


R_PK | C_PK | MAX_NUM

3794 | 1506 | 1.46
3795 | 1512 | 1.62
3796 | 1517 | 0.94
3796 | 1518 | 0.94

Heelllllppp :)

Edited by - Tigerite on 06/23/2003 10:32:20

Edited by - Tigerite on 06/23/2003 10:33:19

Tigerite
Starting Member

30 Posts

Posted - 2003-06-23 : 10:49:25

Agh, I'm half asleep today, the answer was in front of my eyes!


select R.R_PK, MIN(R.C_PK), RM.MAX_NUM
  from (select R_PK, MAX(NUM) MAX_NUM
          from R_TABLE
         group by R_PK) RM
 inner join R_TABLE R
    on R.R_PK = RM.R_PK
   and R.NUM = RM.MAX_NUM
 group by R.R_PK, RM.MAX_NUM

Must admit it's not the nicest though.
Is there a better way of achieving this?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-23 : 11:02:26

quote:

{snip} in this case I only want to return either one (it doesn't matter which) {snip}

Any time you see you've written this, chances are you're working with a poor data model. It should always matter to you which row is selected.

Jonathan
{0}

Tigerite
Starting Member

30 Posts

Posted - 2003-06-23 : 11:48:07

Oh, not in this case, the C_PK is just a word from which I have to return the top 5, to make up a label for a neuron in the self organising map. So it really matters not whether to choose one or the other of the 'best two' words, if you see what I mean.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources