Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

Nested SELECT MAX

I have a table with the following fields site (char), status (char), time (datetime).
This contains info as to wether or not a site in on or offline.

Site          Status       Time
1      on      11:30
1      off      10:30
1      on      09:30

2      off      11:30
2      on      10:30
2      on      09:30

3      on      11:30
3      on      10:30
3      on      09:30

I wish to list thoses sites which have their last status as off.

How can this achieved using a nested select with MAX()?
0
dansker69
Asked:
dansker69
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
HuyBDCommented:
Try this

select site, max(time) from yourtable
group by site,time
0
 
HuyBDCommented:
forget where codition

Try this

select site, max(time) from yourtable
where status='off'
group by site,time
0
 
illCommented:
select site, status
from yourtable a
      join
            ( select site, "time"= max(time) from yourtable      group by site ) b on
                  a.time= b.time and a.site= b.site
where a.status='off'

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
hans_vdCommented:
I think this is more what you are looking for:

SELECT t1.*
  FROM yourtable t1,
       (SELECT site, MAX(TIME) AS max_time
          FROM yourtable
         GROUP BY site
       ) t2
 WHERE t1.site = t2.site
   AND t1.TIME = t2.max_time
   AND t1.status = 'off'
0
 
hans_vdCommented:
ill's solution wasn't there yet when I started typing...
0
 
Scott PletcherSenior DBACommented:
SELECT Site
FROM tableName
GROUP BY Site
HAVING MAX(time) = MAX(CASE WHEN status = 'off' THEN time ELSE '' END)
0
 
Scott PletcherSenior DBACommented:
That should do everything in one pass thru the table, saving a second pass and/or a self-join.

I wasn't sure about the specific format of the time column.  I assumed it was [VAR]CHAR.  If it's a datetime, use '19000101' in the CASE, like so:

MAX(CASE WHEN status = 'off' THEN time ELSE '19000101' END)

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now