Solved

mysql MAX(UID)

Posted on 2015-02-09
25
168 Views
Last Modified: 2015-02-10
I have a query and I am trying to get the MAX Unique Identifier which is a column I created called UID it is Auto incremented
but it continues to return multiples of the same row
SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM Inventory_Parts as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 
 WHERE ip.UID IN(SELECT MAX(UID) FROM Inventory_Parts GROUP BY UID) AND LocationID = 4 and isVisible = 1  GROUP BY UID

Open in new window

UID  | Part DESC                                                      | Part Num  | QTY
1888;"Pressure Transducer Extension Adaptor";"0129-0-00";"0"
1889;"Medium Sleeve Assembly"                          ;"0131-1-00";"10"
1890;"Medium Sleeve Assembly"                          ;"0131-1-00";"20"
When I group by PartNumber it shows the rows with qty 0 and 10
But as you can see the max(UID) is 1890
so it should return rows with qty 0 and 20
0
Comment
Question by:r3nder
  • 15
  • 8
  • 2
25 Comments
 
LVL 6

Author Comment

by:r3nder
ID: 40598991
I have tried this as well
SELECT MAX(ip.UID) as UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM Inventory_Parts as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 
 WHERE LocationID = 4 and isVisible = 1  GROUP BY partnumber

Open in new window

but it returns Qty 0 and 10
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40599015
In which table is UID unique? Inventory_Parts?

It appears that part 0131-1-00 is in the table with 2 different UID's.

Are you wanting the maximum qty for each part number?  That would make some sense.

But the maximum UID for each UID ... doesn't.
0
 
LVL 6

Author Comment

by:r3nder
ID: 40599044
not the max quantity but the maximum UID for each partnumber because the quantity could go down or up in this instance it goes up
0
 
LVL 6

Author Comment

by:r3nder
ID: 40599082
To make it a little clearer
When the quantity is updated for a partnumber a new record is inserted in the datatable with the updated quantity...so as you see in the data from the table. At first it had 10 then  10 more were added. now since that UID will always be greater I have opted to get the last UID for each part that meets that criteria so I can have the right current quantity
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40599186
how's this?

SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM (Select Max(UID) as UID, PartDesc, PartNumber FROM Inventory_Parts group by PartDesc, PartNumber) as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 
WHERE  LocationID = 4 and isVisible = 1

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40599278
That didnt do it - but I found this to get it I think
SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity 
            FROM Inventory_Parts as ip 
            JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 
             WHERE ip.UID IN (SELECT MAX(UID)FROM Inventory_Parts WHERE LocationID = 4 and isVisible = 1 GROUP BY InventoryListID,PartNumber ) and LocationID = 4 and isVisible = 1 GROUP BY PartNumber 

Open in new window

0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40599289
Yours looks wrong ... but I apparently guessed wrong about which table contained the fields in the WHERE clause.

How's this?

SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM (Select Max(UID) as UID, PartDesc, PartNumber FROM Inventory_Parts group by PartDesc, PartNumber
   WHERE  LocationID = 4 and isVisible = 1 ) as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40599341
Inventory_Parts
ConditionID
 InventoryDate
InventoryListID
LastChangeDate
LocationID
Notes
Quantity
UID
Inventory_InventoryList
UID
PartNumber
PartDesc
Obsolete
isVisible
0
 
LVL 6

Author Comment

by:r3nder
ID: 40599377
that didnt work so  I gave you the field names for each tables
0
 
LVL 6

Author Comment

by:r3nder
ID: 40599399
How about this - seems to work
 SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity  FROM Inventory_Parts as ip  JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID   WHERE ip.UID IN (SELECT MAX(UID) FROM Inventory_Parts WHERE LocationID = 0 and isVisible = 1 GROUP BY InventoryListID,PartNumber ) and LocationID = 0 and isVisible = 1 GROUP BY PartNumber 

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40599401
easier to read version
SELECT ip.uid, 
       il.partdesc, 
       il.partnumber, 
       ip.quantity 
FROM   inventory_parts AS ip 
       JOIN inventory_inventorylist AS il 
         ON il.uid = ip.inventorylistid 
WHERE  ip.uid IN (SELECT Max(uid) 
                  FROM   inventory_parts 
                  WHERE  locationid = 0 
                         AND isvisible = 1 
                  GROUP  BY inventorylistid, 
                            partnumber) 
       AND locationid = 0 
       AND isvisible = 1 
GROUP  BY partnumber   

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40599405
Final - does it look right - seems to work
SELECT ip.uid, 
       il.partdesc, 
       il.partnumber, 
       ip.quantity 
FROM   Inventory_Parts AS ip 
       JOIN Inventory_InventoryList AS il 
         ON il.uid = ip.inventorylistid 
WHERE  ip.uid IN (SELECT Max(uid) 
                  FROM   Inventory_Parts 
                  WHERE  locationid = 4 
                         AND isvisible = 1 
                  GROUP  BY inventorylistid, 
                            partnumber) 
    

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40599457
It still looks to me like there's a coordinating condition missing.  If it works, then I won't argue.  But I'd suggest testing it on a larger set of data before concluding that it does work.
0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40599648
Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40600047
You appear to be relying on MySQL's rather unique way of doing GROUP BY, and because this can be changed at server level it could affect you

Does this produce one row, or many rows?

                  SELECT Max(uid)
                  FROM   Inventory_Parts
                  WHERE  locationid = 4
                         AND isvisible = 1
                  GROUP  BY inventorylistid,
                            partnumber

I expect you want multiple rows, but to guarantee it does work that way you may need more in the select clause

                  SELECT  inventorylistid, partnumber, Max(uid)
                  FROM   Inventory_Parts
                  WHERE  locationid = 4
                         AND isvisible = 1
                  GROUP  BY inventorylistid,
                            partnumber

see:
http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html
and:
http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by



-------------
@r3nder, could you do us a favour please?

Please don't use the topic MS SQL Server if your question is for MySQL (and vice versa)
--------------------
0
 
LVL 6

Author Comment

by:r3nder
ID: 40600673
SELECT Max(uid)
                   FROM   Inventory_Parts
                   WHERE  locationid = 4
                   GROUP  BY inventorylistid
This returns 2 records and not the max uid
1888      0
1890      10 <-- should be 20

This returns the exact same result
SELECT  ip.Quantity, il.partnumber, Max(ip.uid)
                   FROM   Inventory_Parts ip
                   Join Inventory_InventoryList il ON il.UID = ip.InventoryListID
                   
                   WHERE  ip.locationid = 4
                          AND il.isvisible = 1
                   GROUP  BY inventorylistid,
                             partnumber
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40600748
Coming back to my query and adjusting it ...

SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM (Select Max(UID) as UID, PartDesc, PartNumber FROM Inventory_Parts group by PartDesc, PartNumber
   WHERE  LocationID = 4  ) as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 
WHERE isVisible = 1

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40600782
This returns all rows
1888      Pressure Transducer Extension Adaptor      0129-0-00      0
1889      Medium Sleeve Assembly      0131-1-00      10
1890      Medium Sleeve Assembly      0131-1-00      20
I changed the query to because some of the fields were not where they were put
SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM (Select Max(UID) as UID,Quantity,InventoryListID  FROM Inventory_Parts PartNumber WHERE  LocationID = 4 group by UID) as ip
JOIN Inventory_InventoryList as il ON il.UID = ip.InventoryListID 
WHERE isVisible = 1

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40600828
OK, I'm having a hard time b/c I didn't start with the table definitions.  Starting again.

SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
FROM (Select Max(UID) as UID, PartDesc, PartNumber FROM Inventory_InventoryList  group by PartDesc, PartNumber
   WHERE isVisible = 1  ) as il
JOIN   Inventory_Parts    as ip ON il.UID = ip.UID
WHERE  LocationID = 4

Open in new window


Wait!  Does UID in one table not match up to the other?  That match was an assumption I made.  If Inventory_Parts really does need to use its InventoryListID to join to Inventory_InventoryList, most of what I'm doing is invalid.
0
 
LVL 6

Author Comment

by:r3nder
ID: 40600914
no the UID in each table do not match each other - UID in Inventory_InventoryList = InventoryListID in Inventory_Parts
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40600958
SELECT ip.UID, il.PartDesc, il.PartNumber,  ip.Quantity
from Inventory_Parts ip Inner Join
  Inventory_InventoryList il on il.UID = ip.InventoryListID  INNER JOIN
(Select Max(p.UID) as UID, l.PartNumber from Inventory_Parts p Inner Join
     Inventory_InventoryList l on l.UID = p.InventoryListID Group By l.PartNumber) M
on M.UID = ip.UID
WHERE LocationID = 4 and isVisible = 1 

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40600995
worked and the data is right :D
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40601007
It's amazing what can happen once the table definition is understood!
0
 
LVL 6

Author Comment

by:r3nder
ID: 40601067
:D  thank you sir
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40601960
and the query is now safe from changes to ONLY_FULL_GROUP_BY setting  as well, all happy!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now