DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more
Try a WHERE clause like this: WHERE ReceivedDate > current date – 7 days
You can simplify your join condition and you need a string_agg() to get the comma separated list of author names: select string_agg(author_name,’,’), count(*) from mas_book_author b join mas_bk_accession_entry e on b.author_id = any(string_to_array(author_ids,’,’)::int[]) where e.author_ids=”1,5″; Online example: http://rextester.com/NVNBH72654 But you should really fix your data model. Storing comma separated values like the author_ids column is … Read more
To get the number of SLAs: select SLA, count(SLA) from table group by SLA
This feature is now made easy in SQL Server 2012. This is working from SQL Server 2012 onwards. Limit with offset to select 11 to 20 rows in SQL Server: SELECT email FROM emailTable WHERE user_id=3 ORDER BY Id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; ORDER BY: required OFFSET: optional number of skipped … Read more
Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren’t used in SQL, but that can vary from database to database. Stick to using single quotes. That’s the primary use anyway. You can use single quotes for a column alias — where you want the column … Read more
I would avoid using SELECT *. Specify columns you actually want even though it may be all of them. SQL Server 2005+ SELECT col1, col2 FROM ( SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow SQL Server 2000 Efficiently Paging Through Large … Read more
Does db/sql batch large datasets on reading into memory ? Is it configurable? [duplicate]
You could try using the Lead() and Lag() SQL Analytic function for this SELECT * from (SELECT *, LAG(AMOUNT) over (partition by user_id order by datetime) as Yesterday_AMOUNT from SALES s ) x where cast(s.datetime as date) = cast(getdaate() as date) and x.AMOUNT <> s.Yesterday_AMOUNT;
The following UPDATE statement will guarantee that each row has a unique MY_TIMESTAMP value, by increasing the milliseconds by the rownum value. EDIT: After Alessandro Rossi pointed out that there could be duplicate values, the following query has been modified to use SYSTIMESTAMP for the update. UPDATE ITEM_HISTORY SET my_timestamp = SYSTIMESTAMP + NUMTODSINTERVAL(rownum/1000, ‘SECOND’); … Read more