Simple Query to Grab Max Value for each ID
Something like this? Join your table with itself, and exclude the rows for which a higher signal was found.
select cur.id, cur.signal, cur.station, cur.ownerid
from yourtable cur
where not exists (
select *
from yourtable high
where high.id = cur.id
and high.signal > cur.signal
)
This would list one row for each highest signal, so there might be multiple rows per id.
Get records with max value for each group of grouped SQL results
There's a super-simple way to do this in mysql:
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.
You avoid complicated subqueries that try to find the max()
etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)
Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.
Version 5.7 update:
Since version 5.7, the sql-mode
setting includes ONLY_FULL_GROUP_BY
by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).
Simple query for getting max value for each entity
If you select for the Max(Date) found on the join, instead of searching a specific Date (Where clause), you will always get the results. Not exactly sure if this is what is asked.
SELECT Cattle.TagNumber,
Cattle.HerdNumber,
Cattle.Breed,
Cattle.DOB,
Cattle.Group,
Weights.Weight,
MAX(Date)
FROM Cattle
LEFT JOIN Weights ON Cattle.TagNumber = Weights.TagNumber
WHERE Cattle.Group = '" + group + "'
Group by Cattle.TagNumber;
Edit
The query should also return values with no weights; you just need to filter the data. You can use Case (it might not work exactly as I wrote it, but something around that). There was the End
missing; also you probably need to convert weight to a nvarchar (or what you chose), otherwise you will get an error when inserting weight
in the column NAMECOLUMN
SELECT Cattle.TagNumber,
Cattle.HerdNumber,
Cattle.Breed,
Cattle.DOB,
Cattle.Group,
(Case when Weights.Weight is null then 'N/A' Else Convert(nvarchar(max),Weights.Weight) End) as NAMECOLUMN,
MAX(Date)
FROM Cattle
LEFT JOIN Weights ON Cattle.TagNumber = Weights.TagNumber
WHERE Cattle.Group = '" + group + "'
Group by Cattle.TagNumber;
Get only max values from table for each ID record
Try using max(l1)over window function:
select distinct max(l1)over (partition by idn),idn,value_varchar
from my_table
order by max(l1)over (partition by idn)
Sample:
create table my_table (l1 number, idn number, value_varchar varchar2(100));
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 224754147, 'adef_node_id_test_99');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 224754148, 'xDSL 1 - node_id atribut');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 244378018, '1');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (2, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (2, 224754147, 'adef_node_id_test_99');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (2, 244378018, '1');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (3, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (3, 244378018, '1');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (4, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (4, 244378018, '1');
Result:
1 224754148 xDSL 1 - node_id atribut
2 224754147 adef_node_id_test_99
4 224754121 node_id_bijelnia_dvorovi
4 244378018 1
SQL to get max value from each group
select * from [table] t1
inner join
(
select track_id, user_id, max(rating) maxRating
from [table]
group by track_id, user_id
) tmp
on t1.track_id = tmp.track_id
and t1.user_id = tmp.user_id
and t1.rating = tmp.maxRating;
SQL select only rows with max value on a column
At first glance...
All you need is a GROUP BY
clause with the MAX
aggregate function:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
It's never that simple, is it?
I just noticed you need the content
column as well.
This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.
It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.
Basically, you have two approaches to solve that problem:
Joining with simple group-identifier, max-value-in-group
Sub-query
In this approach, you first find the group-identifier, max-value-in-group
(already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier
and max-value-in-group
:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
Left Joining with self, tweaking join conditions and filters
In this approach, you left join the table with itself. Equality goes in the group-identifier
. Then, 2 smart moves:
- The second join condition is having left side value less than right value
- When you do step 1, the row(s) that actually have the max value will have
NULL
in the right side (it's aLEFT JOIN
, remember?). Then, we filter the joined result, showing only the rows where the right side isNULL
.
So you end up with:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
Conclusion
Both approaches bring the exact same result.
If you have two rows with max-value-in-group
for group-identifier
, both rows will be in the result in both approaches.
Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".
Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.
How find TOP/MAX value for each id via SQL query in Oracle?
You can use analytic functions:
select *
from (
select
t.*,
row_number() over(partition by id order by repeat_cycle desc) rn
from mytable t
) t
where rn = 1
Alternatively, if there are only three columns in the table, the keep
syntax might be appropriate:
select
id,
max(date) keep(dense_rank first order by repeat_cycle desc) date,
max(repeat_cycle) repeat_cycle
from mytable
Select only one Product with MAX(Id) for each Date
there are many ways to do the same, one of those is to use a row_number function
WITH C AS(
SELECT Date
, Client
, ProductCode
, Price
, ROW_NUMBER() OVER(PARTITION DATE, FOLIO, PRODUCTCODE ORDER BY ID DESC) AS RN
From myTable
)
SELECT Date
, Client
, ProductCode
, Price
FROM C
WHERE RN = 1
What you have to do in this case is create a CTE(It works like a subquery but more readeable) then apply row_number and partition your rows by date,folio,productcode and order it by id, this is going to return you your current list with a rn then filter rn, something good about this is that rn for each case are not repeating.
Related Topics
Delete Duplicate Records in SQL Server
Generate Dates Between Date Ranges
Is There Any Difference Between Is Null and =Null
SQL Statement Error: "Column .. Does Not Exist"
Select Row With Most Recent Date Per User
Calculating Distance Between Two Points (Latitude, Longitude)
MySQL "Group By" and "Order By"
How to Import a Json File into Postgresql
Calculating Cumulative Sum in Postgresql
How to Pivot in Sqlite or I.E. Select in Wide Format a Table Stored in Long Format
What Are Valid Table Names in Sqlite
Where Is |Datadirectory| Defined
How to Simulate Unpivot in Access
Select Statement to Find Duplicates on Certain Fields
SQL Query to Return Differences Between Two Tables