The tag has no wiki summary.

learn more… | top users | synonyms

12
votes
3answers
333 views
+500

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
-1
votes
2answers
118 views

How to select top 10 records from each category

I have a table with fields Hour, PathId, Duration, Event, CellId, Channel I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to ...
0
votes
1answer
27 views

Query with column data for a max

In Postgres, I have a table like this: Column | Type | Modifiers ...
0
votes
2answers
61 views

Find the last visit by teacher and show the rest of columns in MySql

I would like to group the teachers by the last visit and show the other values corresponding to the last visit. CREATE TABLE IF NOT EXISTS `visits` ( `FIELD1` int(5) NOT NULL, `teacher` varchar(9) ...
0
votes
3answers
72 views

Get latest status of an item in the database

I have this table: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | OK 2 | 123 | ... | FAILED ... I want to query get all items which last status was FAILED. ...
2
votes
2answers
256 views

Join 3 tables and return only distinct values for max datetime

I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it ...
1
vote
0answers
40 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
-1
votes
1answer
68 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
2
votes
3answers
812 views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
3
votes
2answers
380 views

Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this SELECT * FROM ( SELECT ...
1
vote
1answer
74 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
0
votes
1answer
41 views

group by and display contents of last row for each result

I've got some data of two objects and I want to retrieve the last row of both the objects, preferably in one query. Table structure: CREATE TABLE data ( pid serial NOT NULL, dt timestamp without ...
4
votes
2answers
220 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
1
vote
1answer
54 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
2
votes
1answer
280 views

Find the second highest value from a subquery or derived table

I have a subquery which returns the following results. 'c003', '120000.00' 'c002', '90000.00' 'c001', '20000.00' 'c005', '8000.00' 'c004', '5000.00' I want to find the second highest value from ...
1
vote
2answers
171 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
2answers
61 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
2
votes
2answers
219 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
7
votes
2answers
4k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
0
votes
2answers
18k views

Select which has max date or latest date

Here are two tables. SCHOOL_STAFF SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID ================================================================= ABE Principal ...
3
votes
2answers
5k views

How group with min(date) and select an another column in the same table

I have a query who return something like it : Name Gender Job date of hire John M mechanic 2012-05-08 John M electrician 2010-01-01 Vicky F scientific 2012-11-11 Bob ...