Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

SQL Query, query?

  • 22-12-2008 10:48AM
    #1
    azzeretti
    Registered Users, Registered Users 2 Posts: 1,477 ✭✭✭


    I have come across a strange problem that I can't get my head around. It involves a SQL table with multiple rows, say about 140,000. The data col in question on the table is called ID# and there are around 55,000 unique ID#'s in the table. There is another col in the table called LINE# and each ID# can have up to 4 LINE#.

    Here is the query. I need to get a query to pull out all the unique ID#'s and ONLY one LINE# attached to it. Does anyone have any ideas? I have written varoius queries and loops, built views and temp_tables but I still can't figure out how to get what I need.

    Any help would be great.


Comments

  • #2
    mercuroman
    Registered Users, Registered Users 2 Posts: 177 ✭✭mercuroman


    Could you use the max statement?
    select id#, Max(line#) from table group by id#


  • #3
    azzeretti
    Registered Users, Registered Users 2 Posts: 1,477 ✭✭✭azzeretti


    mercuroman wrote: »
    Could you use the max statement?
    select id#, Max(line#) from table group by id#


    Umm.....feeling a little silly now......that seems to be the answer!!

    Cheers


  • #4
    KStaford
    Closed Accounts Posts: 815 ✭✭✭KStaford


    SELECT DISTINCT
    ID#, LINE#
    FROM table


  • #5
    mercuroman
    Registered Users, Registered Users 2 Posts: 177 ✭✭mercuroman


    azzeretti wrote: »
    Umm.....feeling a little silly now......that seems to be the answer!!

    Cheers

    No probs - it happens to me loads - get so caught up in a problem that I can't see the answer.
    KStaford wrote: »
    SELECT DISTINCT
    ID#, LINE#
    FROM table

    That won't work because it will bring back every row that has a unique ID# AND Line#, whereas azzeretti is looking for all unique ID#'s and one instance of Line#


  • #6
    KStaford
    Closed Accounts Posts: 815 ✭✭✭KStaford


    mercuroman wrote: »
    No probs - it happens to me loads - get so caught up in a problem that I can't see the answer.



    That won't work because it will bring back every row that has a unique ID# AND Line#, whereas azzeretti is looking for all unique ID#'s and one instance of Line#

    I think it'll bring back each distinct ID# and any instance of LINE#. DISTINCT ID# is one distinct field, the line# is another field in the query

    EDIT:
    oops, sorry fellas, mercuroman you are dead right, my DISTINCT offering will not work and sorry to the op for misleading. As per your earlier solution , here is another variant

    SELECT [ID#], First([LINE#]) AS [FirstOfLINE#]
    FROM Table1
    GROUP BY [ID#]

    The domain aggregate operator FIRST may be a tiny bit quicker than MAX as not as many comparisons have to be made for each read.


  • Advertisement
Advertisement