Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL Query, query?

Options
  • 22-12-2008 10:48am
    #1
    azzeretti
    Registered Users 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

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


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


  • Options
    #3
    azzeretti
    Registered Users 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


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


    SELECT DISTINCT
    ID#, LINE#
    FROM table


  • Options
    #5
    mercuroman
    Registered Users 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#


  • Options
    #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