This question already has answers here: Fetch the row which has the Max value for a column (35 answers) Closed 4 years ago. I’m trying to list the latest destination (MAX departure time) for each train in a table, for example: Train Dest Time 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK … Read more
Suppose I have pandas DataFrame like this: df = pd.DataFrame({‘id’:[1,1,1,2,2,2,2,3,4],’value’:[1,2,3,1,2,3,4,1,1]}) which looks like: id value 0 1 1 1 1 2 2 1 3 3 2 1 4 2 2 5 2 3 6 2 4 7 3 1 8 4 1 I want to get a new DataFrame with top 2 records for each … Read more
I want to return top 10 records from each section in one query. Can anyone help with how to do it? Section is one of the columns in the table. Database is SQL Server 2005. I want to return the top 10 by date entered. Sections are business, local, and feature. For one particular date … Read more
How do you get the rows that contain the max value for each grouped set? I’ve seen some overly-complicated variations on this question, and none with a good answer. I’ve tried to put together the simplest possible example: Given a table like that below, with person, group, and age columns, how would you get the … Read more
Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FROM Table1 Then I could, for example, add a condition to limit intRow to 1 to get a single row with the highest col3 … Read more
I have a table that is a collection entries as to when a user was logged on. username, date, value ————————– brad, 1/2/2010, 1.1 fred, 1/3/2010, 1.0 bob, 8/4/2009, 1.5 brad, 2/2/2010, 1.2 fred, 12/2/2009, 1.3 etc.. How do I create a query that would give me the latest date for each user? Update: I … Read more
Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building indexes? Please use these table/column names in your answer: customer: … Read more
The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2009 p01 4.4 2002 p01 … Read more
Table: UserId, Value, Date. I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle) Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for … Read more
I have a table which I want to get the latest entry for each group. Here’s the table: DocumentStatusLogs Table |ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 | S1 | 8/02/2011 | | 1| 2 | … Read more
