Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    34

    Unanswered: row with highest column A/B/C/D (was "SQL: is this possible?")

    Hi i am wondering if this is possible with SQL:

    Record Nr/Field A/Field B/Field C/Field D
    1/100/200/500/1000
    2/50/300/250/500
    3/50/100/1000/500

    Is there any possibility with SQL to select all data from this table where the following criteria are used:

    Field A highest ? Yes: take this record
    No: Field B highest ? Yes: take this record
    No: Field C highest ? Yes: take this record
    No: Field D highest ? Yes: take this record

    This will result in record 1 to be taken (Field A is highest)

    Other data:

    Record Nr/Field A/Field B/Field C/Field D
    1/100/200/500/1000
    2/100/200/750/500
    3/50/100/1000/500

    This will result in record 2 to be taken (Field C is higest)

    Any help is appreciated, Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If I understand you correctly, this SQL will return all the rows with the one you want at the top:
    Code:
    select * from mytable order by a desc,b desc,c desc,d desc;
    All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if a row (not a record) has 4 columns (not fields), then one of them is going to have the highest value in the row, and so you want that row (not record) to be returned?

    sounds like you want all rows returned, because each row will have a highest column value amongst its 4 column values

    or do you want values compared amongst all rows at the same time? in that case column C in row 2 does not have the highest value

    confused
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Posts
    34
    Quote Originally Posted by andrewst
    If I understand you correctly, this SQL will return all the rows with the one you want at the top:
    Code:
    select * from mytable order by a desc,b desc,c desc,d desc;
    All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.
    I just started to experiment with this This MUST be the solution i was looking for. Thanks

    Edit: I had to select the LAST record to get the correct record (MS Access). Strange, but it works.
    Last edited by irenicuz; 07-20-06 at 06:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •