2

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.

nammae
  • 109
  • 8

4 Answers4

2

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
Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
1

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
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Thanks for the solution. But can you tell me why my 2nd approach doesn't work? – nammae Jan 27 '18 at 02:52
  • Because `having` is designed to be used with `group by`. And in the `having` clause only aggregate functions should be used like `max()` or the columns you group by which you don't do at all. – juergen d Jan 27 '18 at 09:00
0

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;
Joe Ceresini
  • 66
  • 6
0

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.
Sodium
  • 1,016
  • 1
  • 9
  • 22
  • Here is a quiz: What is the value of `amt` in the result record? Good explanation, but very bad query. Consider deleting it (or fixing it) before you start getting downvotes. – Racil Hilan Jan 27 '18 at 04:09
  • Its equivalent to max(seq) which is 5000 here for id=1 in dummy data. FYI, I have posted query to explain use of having clause & keep it simple as well, because @nammae was interested to know why having didn't work for him. – Sodium Jan 27 '18 at 04:33
  • Wrong answer. You failed the quiz :). The value of `amt` is undefined. Meaning, we don't know what the value will be. Read [here for a good explanation of the problem](http://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/). Unlike other databases, MySQL decided not to follow the specs, so no error is raised, but the result is undefined. By the way, you can keep the query as example if you want, just remove the `amt` column. And why do you think that the dummy data doesn't make sense? It looks OK. – Racil Hilan Jan 27 '18 at 04:55
  • The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well. https://dev.mysql.com/doc/refman/5.7/en/select.html Doesn't help in this case but can be useful. – P.Salmon Jan 27 '18 at 09:12
  • Dummy data has duplicate entries for Id..though table structure define it as primary key – Sodium Jan 27 '18 at 09:26
  • The table defines a composite primary key `(id, seq)`, so `id` and `seq` can have duplicate entries as long as their combination is unique. The dummy data is good for the structure. You really should delete that note in your answer, and perhaps do some reading about [composite keys](https://stackoverflow.com/a/26078646). – Racil Hilan Jan 27 '18 at 13:29
  • MySQL has finally addressed the problem and [starting with MySQL 5.7.5, the `ONLY_FULL_GROUP_BY` SQL mode is enabled by default](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes). The reason they gave is rather silly: *because GROUP BY processing has become more sophisticated to include detection of functional dependencies.*, but at least they've changed the default and starting with MySQL 5.7.5, it will behave like most other databases. For earlier versions, if you have access to change the settings, I recommend enabling `ONLY_FULL_GROUP_BY` so you get a clear error. – Racil Hilan Jan 28 '18 at 08:05