I want to find the most recent purchase per customer for each date (a customer might make multiple purchases on a given date and I want the most recent for that date). My dataframe looks something …
Tag: sql-order-by
T-SQL Union (distinct, not all) and Order by with priority
Assume I have a Tag table (Id int and Name nvarchar(100)) with 3 values: 1 Software 2 Hardware 3 Warehouse Now I want to query with keyword, and prioritize the ones that starts with the keyword over …
Why does adding ORDER BY drastically speed up query?
I’ve discovered some very strange and counter-intuitive behaviour in PostgreSQL. I have a query structure as follows. I am selecting both the IDs and the count from a subquery. The subquery does the …
How to join two tables with the same number of rows in SQLite?
I have almost the same problem as described in this question. I have two tables with the same number of rows, and I would like to join them together one by one. The tables are ordered, and I would …
Recursive Subquerying with sorting
I looked at Tim Hall’s excellent article here, that allows you to work with self-referenced entities and show hierarchical data (starting with top level nodes and joining back recursively), using CTE like syntax in Oracle. So I have code that looks like this: For the the anchor rows (the top level hiera…
python sql ORDER date doesn’t work
I ran into another (probably self inflicted) issue. Python3.6 and sqlite I’m very sufficient in creating a db and adding tables and info and all that. My issue is, that the info I’m ading is not …
SELECT DISTINCT ordering rows by order of appearance of the field
I have this data Id Campo ———– ———- 1 id_recibo 2 col_2 3 col_65 4 UNDCAP 5 id_recibo 6 col_2 7 col_65 8 …
How to use order by on a calculated field in SQL?
How to use order by on a calculated field in SQL? This is giving me ‘Total1’ column does not exist but as you see I have created it and is working if I am not using the order by clause. Answer You can do what Mureinik suggests and use the ordinal notation of ORDER BY 13, which means “order b…
Postgres GROUP BY, then sort
I have a database query like: SELECT Foo, Foo2, some_calc as Bar, some_other_calc as Bar2, From FooBar — some inner joins for the calcs GROUP BY FOO ORDER BY Bar DESC, Bar2 DESC; I want …
Is order preserved after UNION in PostgreSQL?
Here is the code: As you can see the recursive code gives the result in right order, but the non-recursive code does not. They both use union, why the difference? Answer Basically, your query is incorrect to begin with. Use UNION ALL, not UNION or you would incorrectly remove duplicate entries. (There is noth…