postgresql - How to get a line of min value

I have table (t_image) with this column datacd | imagecode | indexdate ---------------------------------- A | 1 | 20170213 A | 2 | 20170213 A | 3 | 20170214 B | 4 | 20170201 B | 5 | 20170202 desired result is this datacd | imagecode | indexdate -----

How can you choose the line with the highest value in SQL?

Let's say we have this table: ---------- number |name| price ---------- 1 | a | 2 ---------- 1 | b | 4 ---------- 2 | c | 4 ---------- 3 | d | 6 ---------- I want to pick the numbers and their highest price. So how can i select only the line with num

How to select a particular line of query results

I have a following table in my Oracle database: How can I select the course which is done by most of the students? I am trying multiple variations in following SQL query but it's not working - select count(course) as pcourse, course from studies grou

Combine the crosstab function with DISTINCT ON

I have two tables details and data table. I already joined the two tables and the crosstab function is already done. I want to show only the latest data per serial. Refer to the current and desired output below. Question: How can I use DISTINCT ON in

find the shortest time difference between two dataframes

Suppose I have two dataframes, df1 id time1 1 2016-04-07 21:39:10 1 2016-04-05 11:19:17 2 2016-04-03 10:58:25 2 2016-04-02 21:39:10 df2 id time2 1 2016-04-07 21:39:11 1 2016-04-05 11:19:18 1 2016-04-06 21:39:11 1 2016-04-04 11:19:18 2 2016-04-03 10:5

Get the highest flow marks in SQL

I have 2 tables Table1. StudentMaster ROLL CLASS NAME TOTALMARKS STREAMID ---------- ---------- ------------ ---------- ---------- 12345 5 Rohit 75 100 12346 7 Suman 95 101 12347 5 Rajib 41 100 12348 10 Rakesh 52 102 12349 10 Himesh 101 12350 7 Mizan

Group the most recent records by week

I have an updates table in Postgres is 9.4.5 like this: goal_id | created_at | status 1 | 2016-01-01 | green 1 | 2016-01-02 | red 2 | 2016-01-02 | amber And a goals table like this: id | company_id 1 | 1 2 | 2 I want to create a chart for each compan

SQL by selecting the date closest to the subgroup

I have a table which looks like the following: | ID | Person | Modified On | If I have the following records: {id: 1, person: 'John', Modified On: 2014-06-01 12:00:00}, {id: 2, person 'John', Modified On: 2014-06-02 12:00:00}, {id: 2, person 'Kate',

SQL query with group by clause

I have fetched given below data, now I want the records with Max(Sum1) group by pid but also mention cname of that max value pid cname sum1 23 Abc 946 23 Xyz 920 18 Lmn 900 18 Pqr 1500 now I want As 23 Abc 946 18 Pqr 1500 You can do so by using self

Select a line with the highest order value

For example, I have the table with the next columns: id, fk_id, date, order. How can I select something like this: select id from cards where fk_id = pId and date = pDate; but I need to get only the row with the highest order value. How can I do that

Summation and order at once

I have a table of orders. There I need to find out which 3 partner_id's have made the largest sum of amount_totals, and sort those 3 from biggest to smallest. testdb=# SELECT amount_total, partner_id FROM sale_order; amount_total | partner_id -------

Group by customer and maximum date

I want to list last payments of my customers order by date descending. I wrote a sql query SELECT CARHARTAR, CARHARREFNO, CARHARCARKOD, CARHARCARUNVAN, CARHARACIKLAMA, CARHARTUTAR FROM CARHAR WHERE ( CARHARTIPI IN ( 2, 4 ) ) AND ( CARHARISTIPNO IN (

SQL Server: Largest N per extended group

My table looks like: A B C D 1 1 1 1 1 1 3 2 1 1 0 4 1 1 2 1 1 2 1 0 1 2 0 2 1 2 4 5 2 1 5 3 My goal is to, for each pair of A and B, output the value in D that corresponds to MIN(C), and the value in D that corresponds to MAX(C). The output should b

MySQL - COUNT and retrieve n rows from a subquery

Context: I have an app that shows posts and comments on the home page. My intention is to limit the number of posts shown (ie, 10 posts) and... Limit the number of comments shown per post (ie, 2 comments). Show the total number of comments in the fro

I want the last row of all players in a specific month + year

Here is a simplefied version of my sql table of 2 months (ORDERED BY DATE): player_id | date            | score 1               2011-05-25   1200 2               2011-05-25   3400 3               2011-05-26   3200 4               2011-05-26   4400 1 

MySQL subquery - Find only the first record in a LEFT JOIN

I'm trying to display a list of member records, and I have a few tables I'm using to display what I need. That's the easy part. The part I need help with is with a table that has many records to each member record: Login history I want to display onl

MySQL group by and max returns incorrect rows

I have two tables and I try to find the "post" with the highest score per day. CREATE TABLE IF NOT EXISTS `posts_points` ( `post_id` int(10) unsigned NOT NULL, `comments` smallint(5) unsigned NOT NULL, `likes` smallint(5) unsigned NOT NULL, `fav

selecting limited rows from a child table in a query

Sorry, may be a stupid question. I have 2 tables with parent - child relationship. For each parent, there are more than 10 records in the child table. I want to get only 3 records from child table for each parent. Is it possible with one query in mys

Mysql select separate

I am trying to select of the duplicate rows in mysql table it's working fine for me but the problem is that it is not letting me select all the fields in that query , just letting me select the field name i used as distinct , lemme write the query fo

MySQL: Group by clause

I need some help with a group by mysql query clause. Medals Table (this holds all the medals): +-----------------------------------------------------------------------+ | medal_id | medal_level | medal_name | medal_type | medal_icon | |--------------

Return of the first result where CASE result = X?

I'm trying to return to write a query that only returns first result where someid (result of the first case)=X - and ignoring subsequent results where someid=X. How would I alter the following query to achieve that? Here's what I'm currently using: S

sql join in the join?

I need your help building a SQL statement I can't wrap my head around. In a database, I have four tables - files, folders, folders_files and links. I have many files. One of them is called "myFile.txt". I have many folders. "myFile.txt"

Mysql subquery fails, need help :)

I have a table PICTURES: username varchar(50) picture_id varchar(50) datetime ...and I have a table FRIENDS: user_1 varchar(50) user_2 varchar(50) datetime When you have friends on the website your username goes in user_1, and your friend username's