CODEWITHSUNDEEP
mysqlhaving
nammae
nammae

Reputation: 109

MySQL : Having doesn't work

I am using MySQL. Here is my schema:

Table b

Column Name |   Type   | Primary key
id          |   int    |    Yes
seq         |   int    |    Yes
amt         |   int

Dummy data

id   |  seq  | amt
1    |   1   | 4000
1    |   2   | 3000
1    |   3   | 2000
1    |   4   | 5000   
2    |   1   | 4000
2    |   2   | 3000
3    |   1   | 2000
3    |   2   | 5000

I want to select the record with equivalent id and max value of seq. HERE is my SQL

SELECT b.id, b.seq, b.amt
FROM b
WHERE b.id = 1
AND b.seq = 
(SELECT max(b.seq) FROM b WHERE b.id = 1)

But I wonder if there is more elegant way of achieving what I want. For example,

SELECT b.id, b.seq, b.amt
FROM b
WHERE b.id = 1
HAVING b.seq = max(b.seq)

But it doesn't work as expected. It returns 0 rows.

Upvotes: 2

Views: 1733

Answers (4)

Sodium
Sodium

Reputation: 1066

SQL HAVING Clause

  • HAVING filters records that work on summarized GROUP BY results.
  • HAVING applies to summarized group records, whereas WHERE applies to individual records.
  • Only the groups that meet the HAVING criteria will be returned.
  • HAVING requires that a GROUP BY clause is present.
  • WHERE and HAVING can be in the same query.

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25351

The HAVING clause is to be used with the GROUP BY clause, which is missing in your query. To add a GROUP BY clause to your query, we'll have to include all the fields in the query that don't have an aggregate function, so everything other than seq:

SELECT b.id, b.seq, b.amt
FROM b
WHERE b.id = 1
GROUP BY b.id, b.amt
HAVING b.seq = MAX(b.seq)

Now that will obviously not give your the correct results, because you only want to group by id and not amt. Another problem is that you cannot use the fields that are not in the GROUP BY clause in either the SELECT or HAVING clauses, so you cannot use the seq in those two places, and the query above will give you an error.

If your goal is to get the record for id = 1, then your first query is OK, or better to use the query in juergen's answer. But if your real goal is to select one record for each group, then you can do it like this:

SELECT b.id, b.seq, b.amt
FROM b
INNER JOIN (SELECT id, MAX(seq)
            FROM b
            GROUP BY id) bb ON bb.id = b.id AND bb.seq = b.seq

The result will be:

id   |  seq  | amt
1    |   4   | 5000   
2    |   2   | 3000
3    |   2   | 5000

Upvotes: 2

Joe Ceresini
Joe Ceresini

Reputation: 66

Given your simple example, how about this:

SELECT b.id, b.seq, b.amt
FROM b
WHERE b.id = 1 ORDER BY b.seq DESC limit 1;

Upvotes: 0

juergen d
juergen d

Reputation: 204756

Order the data and take only the first record

SELECT b.id, b.seq, b.amt
FROM b
WHERE b.id = 1
ORDER BY seq desc
limit 1

Upvotes: 1

Related Questions