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: 451
  • Last Modified:

MAX(UID) in MySQL

I am trying to get the max unique identifier from a table
and I keep getting the first id not the last
Select MAX(UID) as id,UploadDate, Tooltype,SerialNumber,LocationID as 'locid' FROM Inventory_SerializedAssets Where LocationID in(" + otherlocations + ") AND ToolType in ('CO','PT','SS','BG','SP','TN') GROUP BY uid ORDER BY id

Open in new window

0
r3nder
Asked:
r3nder
  • 5
  • 2
2 Solutions
 
SimonCommented:
You don't need to GROUP BY UID if you want the MAX value for it.
0
 
Scott PletcherSenior DBACommented:
I'm not an expert on MySQL, but I think this is what you need:


SELECT UID AS id, UploadDate, Tooltype, SerialNumber, LocationID AS 'locid'
FROM Inventory_SerializedAssets
WHERE LocationID in(" + otherlocations + ") AND
      ToolType in ('CO','PT','SS','BG','SP','TN')
ORDER BY UID DESC
LIMIT 1
0
 
r3nderAuthor Commented:
no, sorry that didnt work maybe if  I show you the data

UID | UploadDate              |LocID|SerialNumber|TOOLTYPE|QTY|NOTES                                                 |UserID
842 |2015-02-03 04:22:33|0        |1054                 |CO             |1     |Shipped from Dist 1, received by...|14
843 |2015-02-04 04:27:34|3        |1054                 |CO             |1     |Shipped from Dist 2, received by...|7 <---------This is the one I want
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
r3nderAuthor Commented:
842 |2015-02-03 04:22:33|0        |1054                 |CO             |1     |Shipped from Dist 1, received by...|14
843 |2015-02-04 04:27:34|3        |1054                 |CO             |1     |Shipped from Dist 2, received by...|7 <---------This is the one I want
546 |2015-01-03 04:22:33|0        |1022                 |CO             |1     |Shipped from Dist 17, received by...|14
588|2015-01-04 04:27:34|3        |1022                 |CO             |1     |Shipped from Dist 2, received by...|7 <---------This is the one I want
etc.............
0
 
Scott PletcherSenior DBACommented:
So rather than just the max UID, you need the max UID per SerialNumber?  Or by what criteria?
0
 
r3nderAuthor Commented:
I need the max(UID)  for each serialnumber that matches WHERE LocationID in(3,2,1,4) AND
      ToolType in ('CO','PT','SS','BG','SP','TN')
0
 
r3nderAuthor Commented:
I figured it out
SELECT UID AS id,
              UploadDate,
              Tooltype,
              SerialNumber,
              LocationID AS 'locid'
FROM Inventory_SerializedAssets
WHERE UID IN(SELECT MAX(UID) FROM Inventory_SerializedAssets GROUP BY SerialNumber)  
              AND LocationID in(" + otherlocations + ")
              AND ToolType in ('CO','PT','SS','BG','SP','TN') GROUP BY SerialNumber
0
 
r3nderAuthor Commented:
Thanks for the help
0

Featured Post

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.

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