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;
It very much depends on circumstances and exact requirements. Consider my comment.
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.the_date, p....
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
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
, min(kind) kind
group by val;
ID | VAL | KIND
-: | ---: | ---:
3 | 3 | 4
2 | 1337 | 1
KEEP…FIRST and KEEP…LAST are an Oracle-...
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 "...
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 ...
To get the second highest distinct value in the table you can use
FROM (SELECT DISTINCT TOP (2) value
ORDER BY value DESC)T
/*If only one distinct value return nothing. */
HAVING MIN(value) <> MAX(value);
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
select top 1 DateTime
where StationID = sp.StationID
and ParameterID = sp.ParameterID
order by DateTime desc
To enable ...
I took a slightly different approach, mainly to see how this technique would compare to the others, because having options is good, right?
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 ...
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
WHERE station_id = s.station_id
ORDER BY submitted_at DESC NULLS LAST
dbfiddle here (comparing plans for this query, ...
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
Select Person.PersonName, Email.Email
,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN
left join Email on Person.ID=Email.PersonId
WHERE A.RN = 1
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 ...
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.)
The SQL Server ...
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.
row_number() over(partition by T.CellID ...
I would use an outer apply for this, I find it more readable.
Select Person.PersonName, coalesce(Email.Email,'No email found.') as Email
outer apply (
select top(1) Email.Email
order by <whatever suits you>
) as Email;
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 ...
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 ...
A generic solution can be like below:
;WITH CTE AS
, ROW_NUMBER() OVER (ORDER BY <AnyColumns>) AS RowNum
WHERE RowNum = 2 -- Or any condition which satisfies your problem
Here you can also define ...
I typically use a combination of CTEs and windowing functions. You could achieve this answer using something like the following:
,ROW_NUMBER() OVER (
PARTITION BY CustomerID ORDER BY
--You can change the following field or sort order to ...
Assuming a JSON array in a Postgres 9.4 jsonb column, this would do the job:
SELECT DISTINCT ON (doc->'s') doc
}]'::jsonb AS j
, jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn)
ORDER BY doc-&...
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
group by element
select x.element, x.group_value, x.value
inner join x
on x.element = maxgv.element
and x.group_value = maxgv.maxg
This one uses RANK() function:
with grp ...
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
It's safe to assume you have an equipment table? Then performance won't be a problem:
Based on this equipment table, run a lowly ...
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 ...
The important question which does not seem to be on your radar yet:
From each set of rows for the same seriesName, do you want the columns of one row, or just any values from multiple rows (which may or may not go together)?
Your answer does the latter, you combine the maximum dbid with the maximum retreivaltime, which may come from a ...
There are probably many ways to do this. The first that comes to mind is to use window functions:
( SELECT id, postcode,
ROW_NUMBER() OVER (PARTITION BY id
ORDER BY MAX(date_created) DESC
) AS rn
GROUP BY id, postcode
) AS t
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() ...
Use DISTINCT ON:
SELECT DISTINCT ON (gid)
id, gid, distance, time_interval
WHERE time > 0 -- filtered just to take positive values (orig. Q)
ORDER BY gid, time_interval; -- optionally more expressions to break ties
Select first row in each GROUP BY group?
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)
sELECT X, Y, RowAddedDate, ID
WHERE Rn = 1 ;
If multiple rows have the same date with same X and Y, all of them ...