SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    GROUP BY + SQL Server

    Having a bit of a mental block.

    If I have a table like this:

    code colour
    1234 red
    1235 red
    1236 white
    1237 blue
    1238 blue

    How could return only the first occurence of the colour, along with the code?

    I obviously can't to a group by because I have distinct codes.

    Thanks in advance

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    There is no concept of first in a RDBMS. There is no certainty that the order in which you insert records is the same in which the records are retrieved.

    You can get the code with highest or lowest value within a group, e.g.

    Code:
    select colour, min(code) from t group by colour
    If that is not sufficient you need to explain more.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select color
         , min(code) as code
    from theTable
    group
        by color

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    jinx!

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select top 1 * from Your_table_name order by username desc
    or if you know the code name

    select * from users where code = 1234

  6. #6
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats great, thanks guys!


Bookmarks

Posting Permissions

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