SQL QUERY MAX 2

[ljsmith91]

Hi,

I need some help with a sql query. I have a table that looks like this:

client gbytes backupdate files
abcd 10 2009-01-31 152
abcd 45 2009-02-19 450
abcd 77 2009-02-27 352
abcd 77 2009-03-03 351
bcde 400 2009-01-24 1000
bcde 1005 2009-02-24 1583
bcde 890 2009-02-24 1045

I need to extract all fields for every client but only when the client hits its Max gbytes size. I only want the client, backupdate and files fields output when I hit the MAX gbytes but I am getting multiple lines output for each client. Also, if
MAX gbytes matches for 2 different backupdates then I want the most recent backupdate record.

I have tried variations on the query below but can't seem to find the magic query. Any ideas what I am doing wrong? I also want to get the AVG gbytes in a separate query.

SELECT client,gbytes,backupdate,files
FROM my_table
INNER JOIN
(SELECT client as xclient, MAX(gbytes) AS xMaxBackup
FROM my_table
GROUP BY client)x
ON client = xclient AND gbytes = xMaxBackup

Thanks in advance for any help/ direction.

-ljs

 

[PWise]

SELECT client,gbytes,backupdate,files
FROM my_table
INNER JOIN
(SELECT client as xclient, MAX(gbytes) AS xMaxBackup,max(backupdate) As Mbackup
FROM my_table
GROUP BY client)x
ON my_table.client = x.xclient
AND gbytes = xMaxBackup
And backupdate=Mbackup

 

[bborissov]

[COLOR=blue]SELECT[/color] my_table.client,
       my_table.gbytes,
       my_table.backupdate,
       my_table.files
[COLOR=blue]FROM[/color] my_table
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] client,
                   gbytes,
                   [COLOR=#FF00FF]MAX[/color](backupdate) [COLOR=blue]AS[/color] MaxDate
            [COLOR=blue]FROM[/color] my_table
            [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] client,
                               [COLOR=#FF00FF]MAX[/color](gbytes) [COLOR=blue]AS[/color] gbytes
                        [COLOR=blue]FROM[/color] my_table
                        [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] client) Tbl1
            [COLOR=blue]ON[/color] my_table.Client = Tbl1.Client AND
               my_table.GBites = Tbl1.GBites
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] my_table.client, my_table.GBites) Tbl2
[COLOR=blue]ON[/color] my_table.client     = Tbl2.client AND
   my_table.gbytes     = Tbl2.GBites AND
   my_table.backupdate = Tbl2.MaxDate

Not tested!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ljsmith91]

pwise,

Thanks for the sample. It didn't quite do what I needed and I am not sure why.

bborissov,

Thanks so much. You multi-query seems to have done the trick. I would have never figured that out.

One last question if you have a minute...is there a way of also grabbing top 3 largets gbytes for each client and the dates for those backups rather than just the biggest 1 only ?

-ljs

 

[bborissov]

pwise example wouldn't work just because MAX(date) is not always with that record that have MAX(GBites)
i.e if you have this:
[tt]
Client GBites Date
-------------------
1 1 01 Feb 2009
1 2 01 Jan 2009
[/tt]
The derived table will give you that result:

[tt]
Client GBites Date
-------------------
1 2 01 Feb 2009
[/tt]

Which is not correct.

About TOP 3 for a group just google "TOP N in a group SQL Server"

BTW What version of SQL Server you use?
IN 2005 and 2008 there is a simple way by using some of the new functions introduced there like ROW_NUMBER().


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ljsmith91]

bborissov,

I have MS 2005. If I go after the TOP 3 with their dates then I wouldn't code MAX at all ? How would TOP pull TOP 3 for each client? I thought it was TOP 3 for All queried records. Can you tell I am new to this ?

ljs

 

[bborissov]

[COLOR=blue]SELECT[/color] client,
       gbytes,
       backupdate,
       files
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] client,
             gbytes,
             backupdate,
             files,
             ROW_NUMBER() [COLOR=blue]OVER[/color]([COLOR=#FF00FF]PARTITION[/color] [COLOR=blue]BY[/color] Client [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] GBites [COLOR=#FF00FF]DESC[/color], [COLOR=blue]Date[/color] [COLOR=#FF00FF]DESC[/color]) [COLOR=blue]AS[/color] [COLOR=blue]Row[/color]
      [COLOR=blue]FROM[/color] My_Table) MyTbl
[COLOR=blue]WHERE[/color] [COLOR=blue]Row[/color] < 4

NOT PROPERLY TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Oops, it should be:

[COLOR=blue]SELECT[/color] client,
       gbytes,
       backupdate,
       files
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] client,
             gbytes,
             backupdate,
             files,
             ROW_NUMBER() [COLOR=blue]OVER[/color]([COLOR=#FF00FF]PARTITION[/color] [COLOR=blue]BY[/color] Client [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] GBites [COLOR=#FF00FF]DESC[/color], backupdate [COLOR=#FF00FF]DESC[/color]) [COLOR=blue]AS[/color] [COLOR=blue]Row[/color]
      [COLOR=blue]FROM[/color] My_Table) MyTbl
[COLOR=blue]WHERE[/color] [COLOR=blue]Row[/color] < 4

Copy and paste problem.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ljsmith91]

bborissov,

Looks like that query you provided for Top 3 gbytes works great. One last question, would it be more efficient to use your MAX query or your ROW_NUMBER query with "ROW < 2" to accomplish ONLY listing largest gbytes per client. It looks like the ROW_NUMBER is a simpler query rather than using MAX with 3 queries included in it? Just wondering why I would ever use MAX if we have MS 2005 when that ROW query looks simpler.

Thanks so much once again.

-ljs

 

[bborissov]

Run both with Execution plan turned on and check which is faster.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.