Helpfulbox Now Offers Free POS Software for restaurants, kiosk, and any other retails business completely free used windows cashier system or as full blown POS. You will able to print to kitchen, display orders on the kitchen screen, manage inventory, employee, report, customer loyalty offers, employee management, delivery address track and much more, al for free.

SQL puzzle I'm still getting used to the idea of relational databases. I have a table with columns x, y, and z. I want to select, for each distinct value of x, the value of y that corresponds to the maximum value of z. For instance, if the table looks like this: x|y|z 1|4|3 1|5|0 2|6|2 2|7|3 I want the select to return this: 1|4 2|7 I can get it with a big ugly compound select: SELECT t1.x, y FROM t1 JOIN (SELECT x, MAX(z) AS maxz FROM t1 GROUP BY x) AS t2 ON t1.x = t2.x AND z = maxz; But is there a better way?

By: Guest
Date: Unknown--
Response
0
group

Group by x, y, z

[d] By: Guest
Date: unknown---
Response
0
subquery

The simplest solution is with a subquery:

select x, y, from t1 where z=( select max(z) from t1 )

However there are some ambiguities in the problem statement. It's not clear if "distinct value of x" is really a requirement or that if the table were

x|y|z

1|4|3

1|5|0

2|6|2

2|7|3

2|9|6

whether the correct answer would be:

x|y

2|9

or

x|y

1|4

2|9

[d] By: Guest
Date: unknown---
Response
What is 1 + 100



Helpfulbox Now Offers Free POS Software

for restaurants, kiosk, and any other retails business completely free. It can be used as windows cashier system or as full blown POS. You will able to print to kitchen, display orders on the kitchen screen, manage inventory, employee, report, customer loyalty offers, employee management, delivery address track and much more, all for free.

Just Updated::