SQL query MAX functions

Question

I want to fetch data for a perticular product on basis of last user & respective last used time.Example

Host   Product   LastUserName   LastUsedTime
1      X1        ABC            6/13/2014
1      X1        ABC            6/14/2014
1      X1        ABC            6/15/2014
1      X1        XYZ            6/14/2014
1      X1        XYZ            6/15/2014
1      X1        XYZ            6/16/2014

I have tried MAX function and find data as

Host   Product   LastUserName   LastUsedTime
1      X1        ABC            6/15/2014
1      X1        XYZ            6/16/2014

But unable to get desired output which is

Host   Product   LastUserName   LastUsedTime
1      X1        XYZ            6/16/2014

SQL:

select a.Netbios_Name0, b.DisplayName0, c.LastUserName0,
       MAX(c.LastUsedTime0) as [Last Used Time] 
from table1 a,table2 b,table3 c 
where a.ItemKey = b.ResourceID 
    and a.ItemKey = c.ResourceID 
group by a.Netbios_Name0, b.DisplayName0, c.LastUserName0 
order by b.DisplayName0

Thanks for Help !!

Use `ORDER BY ... DESC` with `LIMIT 1` or `TOP 1` ... instead — zerkms, Jun 19 '14 at 04:23
@Barmar : select a.Netbios_Name0,b.DisplayName0,c.LastUserName0,MAX(c.LastUsedTime0) as [Last Used Time] from table1 a,table2 b,table3 c where a.ItemKey = b.ResourceID and a.ItemKey = c.ResourceID group by a.Netbios_Name0,b.DisplayName0,c.LastUserName0 order by b.DisplayName0 — moronrats, Jun 19 '14 at 05:10
@zerkms: DESC LIMIT 1 not working. even DESC is not changing the output — moronrats, Jun 19 '14 at 05:11
Host referes to Netbios_Name0 and product refers to b.DisplayName0 — moronrats, Jun 19 '14 at 05:55
See in example i have shown in 2nd table .. i am getting a single product which has 2 LastUserName on basis of LastUsedTime respectively. and i Want further filteration which i have shown in table 3 single product, and LastUserName based on Max LastUsedTime. — moronrats, Jun 19 '14 at 06:01
What would be the expected result when two or more users last used the same product on the same day? (e.g. if the record for 16-May-2014 was not in the sample data). Do you include time in the sequence of events? — Kevin Hogg, Jun 19 '14 at 12:04
possible duplicate of [select top 10 records for each category](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category) — Clockwork-Muse, Jun 19 '14 at 12:10
@KevinHogg: Yes possibility is there but that be the least case. but still did not get desired output. — moronrats, Jun 19 '14 at 12:17
@Clockwork-Muse: TOP clause would not work in my case. as i want every product with single user with respective MAXDate — moronrats, Jun 19 '14 at 12:18
@moronrats - Check the first answer, please, which doesn't use the `TOP` clause. This is a classic [tag:greatest-n-per-group] problem; your filtering of `Terminated` users just has to be after getting the greatest row. — Clockwork-Muse, Jun 19 '14 at 12:21

score 1 · Answer 1 · answered Jun 19 '14 at 04:28

1

For MSSQL :

select a.host, a.Product, a.LastUserName, a.LastUsedTime
from tablex a join 
(
select Host, Product, max(LastUsedTime) as LastUsedTime
    from tablex
    group by Host, Product 
) b on a.host = b.host and a.Product = b.Product and a.LastUsedTime = b.LastUsedTime

answered Jun 19 '14 at 04:28

NeedAnswers

1,411
3
19
43

example of sample query select a.Netbios_Name0,b.DisplayName0,c.LastUserName0,MAX(c.LastUsedTime0) as [Last Used Time] from table1 a,table2 b,table3 c where a.ItemKey = b.ResourceID and a.ItemKey = c.ResourceID group by a.Netbios_Name0,b.DisplayName0,c.LastUserName0 order by b.DisplayName0 – moronrats Jun 19 '14 at 05:14

Ajitha Ms · Answer 2 · 2014-06-19T12:39:18.910

0

Try this:

SELECT TOP 1 * FROM table_name ORDER BY LastUsername DESC,  LastUsedTime DESC

edited Jun 19 '14 at 12:39

answered Jun 19 '14 at 04:31

Ajitha Ms

545
5
18

LIMIT is for MySQL, not SQL-Server. – Barmar Jun 19 '14 at 05:57
I have edited my answer. Check whether it works or not. – Ajitha Ms Jun 19 '14 at 12:39

score 0 · Answer 3 · answered Jun 19 '14 at 05:43

0

If you just want one row, don't use GROUP BY. Just order the results and use TOP 1 to get the first row (LIMIT 1 is for MySQL -- this is why you should always tell your RDBMS in questions).

select TOP 1 a.Netbios_Name0, b.DisplayName0, c.LastUserName0, c.LastUsedTime0
from table1 a,table2 b,table3 c 
where a.ItemKey = b.ResourceID 
    and a.ItemKey = c.ResourceID 
ORDER BY c.LastUsedTime0 DESC

answered Jun 19 '14 at 05:43

Barmar

741,623
53
500
612

Thanks for help man !! Its working fine and providing a single row based on LastUsedTime. but as i have shown in example output should on basis of every product ,LastUserName,LastUsedTime respectively – moronrats Jun 19 '14 at 05:53
1

That's not what your example shows. You said the desired output is just one row with the highest `LastUsedTime`. – Barmar Jun 19 '14 at 05:54
You said the output with every product and user name was wrong. – Barmar Jun 19 '14 at 05:58
desired output should be every product with LastUserName and respective LastUsedTime. – moronrats Jun 19 '14 at 06:49
How is that different from what you showed in the question? – Barmar Jun 19 '14 at 06:50
just see the differences in table 2 and table 3 – moronrats Jun 19 '14 at 07:09
I give up, you're too confusing. There is no table 2 and table 3 in the question. – Barmar Jun 19 '14 at 07:16
This fiddle produces what you said is the desired output. http://www.sqlfiddle.com/#!6/7d5c3/1 – Barmar Jun 19 '14 at 07:19

score 0 · Answer 4 · answered Jun 19 '14 at 06:35

Try this:

SELECT      TOP 1 a.Netbios_Name0, b.DisplayName0, c.LastUserName0,
            c.LastUsedTime0 as [Last Used Time] 
FROM        table1 a INNER JOIN
            table2 b ON a.ItemKey = b.ResourceID INNER JOIN
            table3 c ON a.ItemKey = c.ResourceID
WHERE       c.LastUsedTime0 = (SELECT TOP 1 LastUsedTime0 FROM table3 WHERE ResourceID = c.ResourceID)

score 0 · Answer 5 · answered Jun 19 '14 at 07:22

To get the row with the last date per product you first need to get that last date, then JOIN back to the others values

WITH L AS (
  SELECT b.DisplayName0, MAX(c.LastUsedTime0) LastUsedTime0
  FROM   table2 b
         INNER JOIN table3 c ON b.ResourceID = c.ResourceID
  GROUP BY b.DisplayName0
)
SELECT a.Netbios_Name0, b.DisplayName0, c.LastUserName0
     , c.LastUsedTime0 as [Last Used Time] 
FROM   table1 a
       INNER JOIN table2 b ON a.ItemKey = b.ResourceID
       INNER JOIN table3 c ON a.ItemKey = c.ResourceID
       INNER JOIN L ON b.DisplayName0 = L.DisplayName0
                   AND c.LastUsedTime0 = L.LastUsedTime0
ORDER BY b.DisplayName0

if you need to separate also the host name you need to change the query to

WITH L AS (
  SELECT a.Netbios_Name0, b.DisplayName0, MAX(c.LastUsedTime0) LastUsedTime0
  FROM   table1 a
         INNER JOIN table2 b ON a.ItemKey = b.ResourceID
         INNER JOIN table3 c ON a.ItemKey = c.ResourceID
  GROUP BY a.Netbios_Name0, b.DisplayName0
)
SELECT a.Netbios_Name0, b.DisplayName0, c.LastUserName0
     , c.LastUsedTime0 as [Last Used Time] 
FROM   table1 a
       INNER JOIN table2 b ON a.ItemKey = b.ResourceID
       INNER JOIN table3 c ON a.ItemKey = c.ResourceID
       INNER JOIN L ON a.Netbios_Name0 = L.Netbios_Name0
                   AND b.DisplayName0 = L.DisplayName0
                   AND c.LastUsedTime0 = L.LastUsedTime0
ORDER BY b.DisplayName0

As a last advice please stop to write the JOIN definition in the WHERE clause, it's less readable and mix to different ideas

I have the same thought "To get the row with the last date per product you first need to get that last date" but can you explain "WITH L" ? I didn't get that. — moronrats, Jun 19 '14 at 09:04
I have created schema. please check [link](http://www.sqlfiddle.com/#!6/25683/1) just check with your query for desired output, i have tried that too. — moronrats, Jun 20 '14 at 03:36

score 0 · Answer 6 · edited Jun 19 '14 at 12:24

0

 SELECT Product_Usage.*
   FROM Product_Usage
        INNER JOIN (SELECT Product, max(LastUsedTime) LastUSedTime
                      FROM Product_Usage
                     GROUP BY Product) LastUsed
                ON Product_Usage.Product = LastUsed.Product
               AND Product_Usage.LastUsedTime = LastUsed.LastUsedTime

edited Jun 19 '14 at 12:24

Kevin Hogg

1,771
25
34

answered Jun 19 '14 at 07:47

anonxen

794
1
8
24

cdummy · Answer 7 · 2014-06-19T11:48:42.707

0

;with cte as ( select max(lastusedtime) over (PARTITION by product) as mdate ,host,product,lastusername ,lastusedtime from test1 ) select distinct host,product,lastusername ,lastusedtime from cte
where lastusedtime=mdate enter image description here

edited Jun 19 '14 at 11:48

answered Jun 19 '14 at 09:02

cdummy

455
1
4
14

If there are 2 products X1 and X2. this query giving output any of the product which has maxdate. but as per desired output both of the products should be present into the output with their respective lastusername and maxdate – moronrats Jun 19 '14 at 10:05
Your desired output is one row with max date according to ur question.Am i right? – cdummy Jun 19 '14 at 10:17
depends on products. if 1 product then 1 output if 2 product then 2 output – moronrats Jun 19 '14 at 10:35
i ran the query on fiddle **with TEMP as ( Select MAX(c.LastUsedTime) OVER (PARTITION BY b.Product) as mdate,a.Host, b.Product,c.LastUserName,c.LastUsedTime From table1 a INNER JOIN table2 b ON table1.itemkey = table2.resourceid INNER JOIN table3 c ON table1.itemkey = table3.resourceid ) Select Distinct Host,Product,LastUserName,LastUsedTime from TEMP Where LastUserName = mdate** but its throwing error of multi-part identifier. [link](http://www.sqlfiddle.com/#!6/25683/1) – moronrats Jun 20 '14 at 03:35

SQL query MAX functions

7 Answers7