none
Fetching the last 5000 entries in two SQL queries and getting the results in one grid?

    Question

  • This is utilizing MSSQL 2000 (Enterprise Manager / Query Analyzer), sorry if this is the wrong section.

    I've got two SQL queries that I need to merge into one result grid. The query needs to fetch the last XXXX (5000 in this case) entries in the table for two separate IP addresses and the results of which merged into one grid for data export. Here's the code I've been trying to use, however when utilizing UNION ALL I get a syntax error. Here's what the code is, with IP addresses modified for obvious reasons. The queries pull up exactly how I want them when ran separately, however I get the aforementioned error when trying to run them together with a union.

    SELECT a.ApptSessions AS '192.168.1.1'
    FROM 
    (
    SELECT TOP 5000 ID, ApptSessions
    FROM MonitorInfo 
    WHERE Hostname= '192.168.1.1' 
    ORDER BY ID DESC 
    ) a
    ORDER BY ID ASC
    
    UNION ALL
    
    SELECT b.ApptSessions AS '192.168.2.2'
    FROM 
    (
    SELECT TOP 5000 ID, ApptSessions
    FROM MonitorInfo 
    WHERE Hostname= '192.168.2.2' 
    ORDER BY ID DESC 
    ) b
    ORDER BY ID ASC


    • Edited by McNuggit23 Wednesday, July 24, 2013 8:13 PM
    Wednesday, July 24, 2013 8:12 PM

All replies

  • What error message do you receive?

    If you remove the ORDER BY ID ASC both places it work? 

    If you remove the first occurance of "ORDER BY ID ASC" and add a.ID to the first select, and b.ID to the second select is also work.


    Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)

    Wednesday, July 24, 2013 9:51 PM
  • I get the following error when running the above code:

    "Server: Msg 156, Level 15, State 1, Line 11
    Incorrect syntax near the keyword 'UNION'."

    When I remove the first occurance, and add the a.ID and b.ID I only get two columns in my grid:

    192.168.1.1 and ID. The order of these is incorrect as well, it fetches the last 5000, but doesn't put the most recent entry on top, which is what I put the first occurance in there for.

    EDIT: I have set the last ORDER BY to DESC, and it now shows in the correct order. However, I still have the issue with both 192.168.1.1 and 192.168.2.2 showing up in one grid with both answers mixed in, and ID showing in another row. I need the ID to NOT show, and for 192.168.1.1 to have a seperate results row from 192.168.2.2.


    • Edited by McNuggit23 Thursday, July 25, 2013 2:31 PM See above.
    Thursday, July 25, 2013 2:12 PM
  • I puzzled over this a while.  How do you want these rows to look when merged?  Do you care what IP address it comes from?  Or just overall order?  I am assuming "overall order" based on your comments.

    Your comment about both data sets showing up in "one grid" is quite reasonable since you are only returning one column  and doing one sort on ID DESC, so naturally they are all stirred together in ID order.  Do you want two columns side by side?  How will you tell apart which row is from source xxx.1 and which is from source xxx.2?

    If you show a sample result set of just a few rows it would be helpful.   One possible (wild guess) answer could be:

    SELECT a.ApptSessions  AS '192.168.1.1', b.ApptSessions AS '192.168.2.2'
    FROM 
    (
    SELECT TOP 5000 ID, ApptSessions
    FROM MonitorInfo 
    WHERE Hostname= '192.168.1.1' 
    ORDER BY ID DESC 
    ) a
    FULL OUTER JOIN
    (
    SELECT TOP 5000 ID, ApptSessions
    FROM MonitorInfo 
    WHERE Hostname= '192.168.2.2' 
    ORDER BY ID DESC 
    ) b
    ON a.ID = b.ID 
    ORDER BY COALESCE(a.ID, b.ID) DESC

    If this is not the answer, please describe clearly with sample rows and columns what you are expecting to see.

    RLF

    Monday, July 29, 2013 3:31 PM
  • Sorry for the delayed reponse - I had lost access to my MSDN account.

    Here is what I get running the following code:

    SELECT a.ApptSessions AS '192.168.1.1'
    FROM 
    (
    SELECT TOP 5000 ID, ApptSessions
    FROM MonitorInfo 
    WHERE Hostname= '192.168.1.1' 
    ORDER BY ID DESC 
    ) a
    ORDER BY ID ASC
    
    SELECT b.ApptSessions AS '192.168.2.2'
    FROM 
    (
    SELECT TOP 5000 ID, ApptSessions
    FROM MonitorInfo 
    WHERE Hostname= '192.168.2.2' 
    ORDER BY ID DESC 
    ) b
    ORDER BY ID ASC

    I get my results like this:

    http://i.imgur.com/ifch9SR.png

    I would like to get my results like this:

    http://i.imgur.com/5ShSmDU.png

    It should be noted that I'm also expecting to add a bit less than a dozen more servers to this query as well. Does that make sense? I do appreciate your help.

    Friday, August 16, 2013 4:19 PM
  • Here is one example which should work on 2000 - and I shouldn't need to tell you that 2000 is completely unsupported and, as such, is becoming less familiar with anyone reading your questions. In addition, it will certainly help others if you includes links in your post as actual links (that then become clickable). 

    select *
    from (select top 10 object_id, '192.168.1.9' as IP  from sys.objects order by object_id desc) as x 
    union all 
    select * 
    from (select top 10 object_id, '192.168.1.2' as IP   from sys.objects order by object_id ) as y
    order by IP, object_id

    Friday, August 16, 2013 5:12 PM