73

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDate FROM Production.TransactionHistory h WHERE h.ProductID = 800; ...


52

It very much depends on circumstances and exact requirements. Consider my comment to the question. Simple solution With DISTINCT ON in Postgres: SELECT DISTINCT ON (i.good, i.the_date) i.the_date, p.the_date AS pricing_date, i.good, p.price FROM inventory i LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date ORDER BY i.good, i....


47

The typical way to do this in SQL Server 2005 and up is to use a CTE and windowing functions. For top n per group you can simply use ROW_NUMBER() with a PARTITION clause, and filter against that in the outer query. So, for example, the top 5 most recent orders per customer could be displayed this way: DECLARE @top INT; SET @top = 5; ;WITH grp AS ( ...


40

This solution also uses keep, but val and kind can also be simply calculated for each group without a subquery: select min(id) keep(dense_rank first order by kind) id , val , min(kind) kind from mytable group by val; ID | VAL | KIND -: | ---: | ---: 3 | 3 | 4 2 | 1337 | 1 dbfiddle here KEEP…FIRST and KEEP…LAST are an Oracle-...


29

Your current query is not giving the desired result because you are using a GROUP BY clause on the PERSON_ID column which has a unique value for both entries. As a result you will return both rows. There are a few ways that you can solve this. You can use a subquery to apply the aggregate function to return the max(LAST_UPDATE_DATE_TIME) for each ...


24

In DBMS, like MySQL, that do not have window functions or CROSS APPLY, the way to do this would be to use standard SQL (89). The slow way would be a triangular cross join with aggregate. The faster way (but still and probably not as efficient as using cross apply or the row_number function) would be what I call the "poor man's CROSS APPLY". It would be ...


24

To get the second highest distinct value in the table you can use SELECT MIN(value) FROM (SELECT DISTINCT TOP (2) value FROM tablename ORDER BY value DESC)T /*If only one distinct value return nothing. */ HAVING MIN(value) <> MAX(value);


24

Assuming the highest value only occurs once, another way would be to use OFFSET (SQL Server 2012 or later): SELECT * FROM tablename ORDER BY column DESC OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;


23

If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this: select StationID, ParameterID, m.DateTime LastDate from StationParameter sp cross apply ( select top 1 DateTime from MyTable where StationID = sp.StationID and ParameterID = sp.ParameterID order by DateTime desc ) m To enable ...


21

DISTINCT ON() Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT) SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "...


21

I took a slightly different approach, mainly to see how this technique would compare to the others, because having options is good, right? The Testing Why don't we start by just looking at how the various methods stacked up against each other. I did three sets of tests: The first set ran with no DB modifications The second set ran after an index was ...


19

For only 400 stations, this query will be massively faster: SELECT s.station_id, l.submitted_at, l.level_sensor FROM station s CROSS JOIN LATERAL ( SELECT submitted_at, level_sensor FROM station_logs WHERE station_id = s.station_id ORDER BY submitted_at DESC NULLS LAST LIMIT 1 ) l; dbfiddle here (comparing plans for this query, ...


18

APPLY TOP or ROW_NUMBER()? What could there possibly be more to say on that matter? A short recap of the differences and to really keep it short I will only show the plans for option 2 and I have added the index on Production.TransactionHistory. create index IX_TransactionHistoryX on Production.TransactionHistory(ProductID, TransactionDate) The ...


18

A full analysis would require access to the execution plans, table and index definitions, and database statistics (or a copy of the database itself). That's possibly unrealistic, so here's some general observations, and a possible solution for you to try. (Strictly, this question is probably beyond this site's remit.) General background The SQL Server ...


18

SELECT A.PersonName, A.Email FROM ( Select Person.PersonName, Email.Email ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN From person left join Email on Person.ID=Email.PersonId ) A WHERE A.RN = 1


17

If you aim is to have queries with maximum efficiency, none of the above queries is really the best. Not always at least. Efficiency depends on many different things, like the specific DBMS, the specific version (different versions have different improvements on the optimizer and the available syntax), the type of columns, the indexes available, the size of ...


14

Use row_number() in a derived table. Partition by CellID and use the order by as per your specification. In the main query you filter on rn to get the top 10 rows per category. select T.CellID, T.PathID, T.Duration from ( select T.CellID, T.PathID, T.Duration, row_number() over(partition by T.CellID ...


13

I would use an outer apply for this, I find it more readable. Select Person.PersonName, coalesce(Email.Email,'No email found.') as Email From person outer apply ( select top(1) Email.Email from Email where Person.ID=Email.PersonId order by <whatever suits you> ) as Email;


13

A generic solution can be like below: ;WITH CTE AS ( SELECT Col1 , Col2 , <AnyColumns> , ROW_NUMBER() OVER (ORDER BY <AnyColumns>) AS RowNum FROM <YourTable> WHERE <YourCondition> ) SELECT * FROM CTE WHERE RowNum = 2 -- Or any condition which satisfies your problem Here you can also define ...


13

This is related to "Index Skip Scan" optimization (see the Connect item below, from 2011). Unfortunately it has been closed as "Won't Fix". Some related enhancements are already there but only for partitioned tables: Query Processing Enhancements on Partitioned Tables and Indexes. Various workarounds exist though: Workaround / solution 1: CROSS APPLY to ...


12

First answer uses a CTE to select max(group_value) and then join with the table. with maxgv as ( select element, max(group_value) maxg from x group by element ) select x.element, x.group_value, x.value from maxgv inner join x on x.element = maxgv.element and x.group_value = maxgv.maxg ; This one uses RANK() function: with grp ...


11

I typically use a combination of CTEs and windowing functions. You could achieve this answer using something like the following: ;WITH GiveMeCounts AS ( SELECT CustomerID ,OrderDate ,TotalAmt ,ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY --You can change the following field or sort order to ...


11

Assuming a JSON array in a Postgres 9.4 jsonb column, this would do the job: SELECT DISTINCT ON (doc->'s') doc FROM ( SELECT '[ { "id":1, "s":"foo" }, { "id":2, "s":"bar" }, { "id":3, "s":"foo" }]'::jsonb AS j ) t , jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn) ORDER BY doc-&...


11

Unless you are in a very old version of Postgres, you don't need the double join. You can get the same result by using a LATERAL join. The duplicate results can be avoided in your method by adding a second condition besides the rec.id = rech2.record_id. With the LATERAL join method, the use of LIMIT is avoiding it anyway. There can be only 1 row returned ...


10

A plain multicolumn b-tree index should work after all: CREATE INDEX foo_idx ON geoposition_records (equipment_id, created_at DESC NULLS LAST); Why DESC NULLS LAST? Unused index in range of dates query Function If you can't talk sense into the query planner, a function looping through the equipment table should do the trick. Looking up one equipment_id ...


10

I infer that your data looks like this: Person Table ╔══════════╦═══════╦════════╗ ║ PersonID ║ Name ║ Gender ║ ╠══════════╬═══════╬════════╣ ║ 1 ║ John ║ M ║ ║ 2 ║ Vicky ║ F ║ ║ 3 ║ Bob ║ M ║ ╚══════════╩═══════╩════════╝ Job Table ╔══════════╦═════════════╦════════════╗ ║ PersonID ║ JobName ║ HireDate ║ ╠═══...


10

Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the version you need to support, using a version-specific tag): ;WITH cte AS ( SELECT Id, EffectiveDate, SequenceId, CustomerId, AccountNo, rn = ROW_NUMBER() ...


10

Preliminary notes You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related: Would index ...


10

You can use window (ranking). functions for this: WITH ct AS ( SELECT X, Y, RowAddedDate, ID, Rn = DENSE_RANK() OVER (PARTITION BY X, Y ORDER BY RowAddedDate DESC) FROM dbo.MyTable ) sELECT X, Y, RowAddedDate, ID FROM ct WHERE Rn = 1 ; If multiple rows have the same date with same X and Y, all of them ...


10

You can use ROW_NUMBER: with cte as ( select *, RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC) from un_hi where ag_id = 'bfd' and cdts > '201708' and unit_status != 'uc' ) select * from cte where RN = 1;


Only top voted, non community-wiki answers of a minimum length are eligible