SQL query help

Discussion in 'Programming' started by Chenks, Apr 10, 2013.

Share This Page

  1. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    at the moment i've got 2 querys

    1st query lists the results from the table
    Code:
    SELECT field1, field2, field3 FROM dbo.blah LEFT JOIN dbo.blah2 ON dbo.blah.field10=dbo.blah2.field3 WHERE field1='blah' ORDER BY blah
    2nd query is basically the same except all it does it count the number of results
    Code:
    SELECT COUNT(*) AS count_alias FROM blah WHERE field='blah'
    so running 2 querys seems a bit wastefull so can these 2 be combined into 1 query that produces the same results as the 2?
     
  2. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    just thought it might lessen the load if it was done via one query
     
  3. brumster

    brumster Midland Muppet

    Joined:
    Jun 2, 2004
    Messages:
    3,635
    Likes Received:
    0
    Deleted my first post as I was talking crap :D

    There is in MSSQL via the ROW_NUMBER function...? Google it ;)

    I wouldn't worry too much about issuing two queries - caching and indexing shouldn't mean it hits the server all that much or, if they're massive tables, keep a table with current index values stored in it, and query that when you need to know... risks being out of date, though.

    Thought : or if you're bringing the results into a recordset-type object (not sure what you're working in here) there may be a function on that object to return the number of records within it, negating the need to query the DB again.
     
  4. latency

    latency Existentialist

    Joined:
    Jul 1, 2004
    Messages:
    8,780
    Likes Received:
    0
    This in PHP?

    mysql_num_rows($query) returns the number of results, but it'll run the query again, so you're still calling the DB twice.

    Would need to know the context to offer a precise example, but you could either do;

    Code:
    $query = mysql_query = "SELECT field1, field2, field3 FROM dbo.blah LEFT JOIN dbo.blah2 ON dbo.blah.field10=dbo.blah2.field3 WHERE field1='blah' ORDER BY blah;"
    
    $rowcount = 0;
    
    while ($result = mysql_fetch_array($query) {
    $rowcount++
    
    };
    
    echo $rowcount. 'rows found';
    
    Or if you're handling the entire array as part of something else;

    Code:
    $results = mysql_fetch_array(mysql_query="SELECT field1, field2, field3 FROM dbo.blah LEFT JOIN dbo.blah2 ON dbo.blah.field10=dbo.blah2.field3 WHERE field1='blah' ORDER BY blah;"));
    
    $rowcount = count($results['field1']);
    
    
    
    
     
  5. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    Nope no php.
    Windows server with MSSQL.
     
  6. Nanaki

    Nanaki Nanaki

    Joined:
    Sep 18, 2004
    Messages:
    567
    Likes Received:
    1
    Your first query is more of a raw data query rather than aggregate data so merging the two into one query isn't really simple or easy to display if your objective is to reduce load impact.

    You could modify the first query to use count in its select statement and use a group by clause to get aggregate results but if you still need the main result set that's not much use.

    You could "select field1, field2 into #tablename from etc..." your first query into a temp # table and then run your count against it so your not querying your live data feed if that is an issue. It then allows you to do further work against it without all your joins having to run each time.

    If you are doing a very basic count on a table though and you have an identity / uniqueid field I would always recommend running your count like so.

    "select count(uniqueid) as Total from tablename" as it is lighter on the database than count(*) as it should only have to hit the index rather than the main table as long as your tables are indexed correctly.

    Probably shouldn't be answering SQL questions while watching F1 practive 3 at 4:20am but I'm still pretty sure everything above makes sense.

    Let me know if you have any more questions.
     
  7. Samsonite

    Samsonite Mine's a frosty one

    Joined:
    Oct 28, 2004
    Messages:
    1,025
    Likes Received:
    0
    What is the use case for this and what are you ultimately trying to achieve? On the face of it, the question is almost asking:

    How do I combine

    value, value, value
    value, value, value
    value, value, value


    With

    100

    I'm sure you can see the issue!

    2 Queries is not inefficient by any means, but you have to be careful about why your are using COUNT(). Because you get a result set with a number of rows, you then do a separate COUNT() query with the same WHERE clause - the count could be a different number to the number of rows you got first time round - you are only assuming that another row has not been added or taken away in the split second between the 2 queries. Now, you probably know if this is likely, but best practice in this case would be to count the rows of the result set because that seems to be what you want to do.

    Nanaki's tips on using a #temp table and doing the COUNT() on an ID column is sound advice if you are purely using SQL.
     
  8. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    OK what i'm doing is this.

    the query
    Code:
    SELECT field1, field2, field3 FROM dbo.blah LEFT JOIN dbo.blah2 ON dbo.blah.field10=dbo.blah2.field3 WHERE field1='blah' ORDER BY blah
    displays the results in a table on a web page (using a DO/WHILE loop to list them all).

    the second COUNT query i was using to simply display the number of records as a number on the same webpage.

    you're right in saying that there is a chance (albeit a very slim one) that in the time it takes between the 2 queries running, a new record might have added to the database, this making the COUNT query not match the first query.

    so i hear what you're saying in that i should be running the count based on the results of the first query.

    so how would i do that? each result in the first query does have a unique field (say field1).

    btw, this is all MSSQL and being displayed on a webpage using ASP/VB.
     
  9. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    so does this look correct?

    Code:
    SELECT dbo.Calls.Call_Ref, dbo.Clients.Co_Name
    INTO #SGTemp
    FROM dbo.Calls with (nolock)
    LEFT JOIN dbo.Clients ON dbo.Calls.Link_to_Client=dbo.Clients.Client_Ref
    WHERE Link_to_Contract_Header = 'VAN-1'
    ORDER BY Call_Ref ASC
    I'm assming this creates a virtual table with the results of the query?
    where does that virtual table reside? and when does it get dropped (if at all)?
     
  10. Samsonite

    Samsonite Mine's a frosty one

    Joined:
    Oct 28, 2004
    Messages:
    1,025
    Likes Received:
    0
  11. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    OK, got another query question to ask.

    have the following query

    Code:
    select Call_Ref, Date_Received+Time_Received as 'Date/Time', Co_Name, Alias, Link_to_Contract_Header, Call_Type, attended
    from dbo.Calls with (nolock)
    inner join dbo.Clients with (nolock) on dbo.Calls.Link_to_Client = dbo.Clients.Client_Ref
    left outer join (select link_to_call, min(on_site) attended
    	from call_events with (nolock) where event_code in ('PR','F','RD','NA')
    	group by link_to_call) vis
    	on call_ref=link_to_call
    where Link_to_Contract_Header = 'ARHA-1'
    and Call_Type in ('B','RC','FS')
    and Date_Received >= '1 May 2013'
    and Last_Event_Status not in ('X')
    order by 'Date/Time'
    which gives me 44 results.
    i want to narrow those results down so that it only shows me results where the "attended" date/time (which is min(on_site) in the query) is either between 5pm and 8:30am on a mon-fri or is on a weekend day (sat or sun).

    thoughts?
    ps. it's MS SQL
     
  12. Samsonite

    Samsonite Mine's a frosty one

    Joined:
    Oct 28, 2004
    Messages:
    1,025
    Likes Received:
    0
    I think you will want to use on_site rather than min(on_site) for the WHERE clause.

    You want to make use of http://www.w3schools.com/sql/func_datepart.asp and although it's easy to understand the names of days, it is more efficient to stick with the numbers:

    Code:
    select datename(dw,getdate()) --Friday
    select datepart(dw,getdate()) --6
    So in your WHERE clause, you want to add something along the lines of:

    Code:
    AND
    (
    (datepart(dw,on_site) IN (2,3,4,5,6)) -- Monday to Friday
    AND
    ((datepart(hh,on_site) >= 17) AND (datepart(hh,on_site)
     
  13. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    need to use min(on_site) as i only want to return the lowest record from that table in the second select.

    in the end i just ran 2 separate queries (1 with the weekend results and 1 with the weekday out of hours results). it was just going into a spreadsheet for a report so didn't really make any difference how i got the data.

    i'll post the queries i used tomorrow when i'm back on the pc.
     
  14. Nanaki

    Nanaki Nanaki

    Joined:
    Sep 18, 2004
    Messages:
    567
    Likes Received:
    1
    You need to deal with this type of thing as two sections. Date and time rarely play well in the same where clause.

    First thing to check is if your server is set to use sunday as 1 or monday as 1.

    Code:
    select DATEPART(dw,getdate())
    
    On my servers Sunday is 1, Monday 2 etc...

    Using that add the following to make sure you get the right days

    Code:
    and DATEPART(dw,vis.attended) in ('1','7') --Weekend
    and DATEPART(dw,vis.attended) not in ('1','7') --Weekday
    
    To get the right times is a bit more complex. See what your asking directly doesn't work in SQL. Time doesn't like crossing midnight in a single request.

    This means you need to ask for times before 8:30am and after 5pm.

    After 5pm is relatively easy.

    Code:
    and DATEPART(hh,vis.attended) >= '17'
    
    Before 8:30 am is a little trickier as minutes are involved.

    You could do something like:

    Code:
    and (
          DATEPART(hh,vis.attended) < '8' 
          or (DATEPART(hh,vis.attended) = '8' and DATEPART(n,vis.attended) < '30')
           )
    
    Now don't get me wrong. I am a little rusty in all of this. Been a couple of years since i did this stuff on a production environment even though I am sitting my 2012 exams.

    There is possible a better way to deal with time on the newer version of SQL but I mostly used 2000 and 2005.

    Using all of the above you could try this and clause to your query if I've picked the right field. If not please change accordingly.

    Code:
    and
    	(DATEPART(dw,vis.attended) in ('1','7') --Weekend
    	 or	(DATEPART(dw,vis.attended) not in ('1','7') --Weekday
    		and DATEPART(hh,vis.attended) >= '17') -- 5pm or later
    	 or (DATEPART(dw,vis.attended) not in ('1','7') --Weekday
    	     and (DATEPART(hh,vis.attended) < '8' -- Before 8am
    			  or (DATEPART(hh,vis.attended) = '8' -- Between 8am and 8:30am
    			      and DATEPART(n,vis.attended) < '30'
    			      )
    			  )
    		 )
    	 )
    
    Let me know how you get on.
     
  15. Nanaki

    Nanaki Nanaki

    Joined:
    Sep 18, 2004
    Messages:
    567
    Likes Received:
    1
    or completely ignore me as samsonite posted while i was working it out lol.