Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

choosing oldest valued cell when 2 columns have same value but third column has differing values (SQL)

J
juvenalb last edited by

In SQL, I have three columns, the first two are values to create distinction for a person. The third column will have different values for each person (dates). How do I select the row for each person with the oldest valued date?

should output:
John Plumber 1/3/2020
Elizabeth Teacher 12/1/2020
Reply Quote 0
1 Reply Last reply

tahishae

SELECT First Name, 
       Job, 
       MIN(Day of material)  
FROM table  
GROUP BY First Name, Job

EASY!!!

distinct 10

2
Posts

0
Views

Suggested Topics

E

How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL?
SQL, Database Testing • postgresql • • emran

2

0
Votes

2
Posts

0
Views

J

Another solution. Recursive part creates groups of rows of status "absent". The solution uses recursive CTEs and window functions. WITH RECURSIVE a(id, name, date, n) as ( SELECT id, name, q.date, 1 as n FROM ( SELECT id, name, date, date-lag(date) OVER (PARTITION BY name ORDER BY date) as lag FROM attendance WHERE status='absent' ) q WHERE lag >1 or lag is null UNION SELECT a.id, a.name, a.date, a.n + 1 FROM a JOIN attendance at ON ( a.id = at.id and at.name = a.name and at.date = a.date + n) WHERE at.status='absent' ) SELECT id, name, sum(long_absences) FROM ( SELECT id, name, count(*)/3 as long_absences FROM a GROUP BY id, name, date having count(*) >=3 ) as absences GROUP BY id, name;
K

List out distinct attributes in one column for distinct IDs
SQL, Database Testing • mysql distinct • • Kadir

2

0
Votes

2
Posts

1
Views

G

SELECT id, GROUP_CONCAT(company SEPARATOR ", ") AS companies FROM `order` GROUP BY id
C

PostgreSQL DISTINCT ON forces a specific order
SQL, Database Testing • postgresql distinct • • charlenef

2

0
Votes

2
Posts

1
Views

J

Why DISTINCT ON is sorting the rows? Distinct removes "duplicate" rows from the result set. That's a whole lot easier to do if the items being compared arrive neatly sorted into order. Check the Explain Plan for this query - it will probably contain extra "Sort" and "Unique" steps. Guess what those are doing. "select distinct" is always a Red Flag for me. It is almost always used poorly, a band-aid to get around a bigger problem and introduces problems of its own. Remember, the performance of ... select distinct a, b, c . . . ... is roughly comparable to that of ... select a, b, c . . . group by a, b, c order by a, b, c Ask yourself why [you think] you need to use "distinct".
C

How to query results where a column with 2 classifications has same value in another column in the same table?
SQL, Database Testing • sql server • • courtlanda

2

0
Votes

2
Posts

0
Views

A

Something like this: select a.columna, a.email from (select * from tablename where columna='A') a join (select * from tablename where columna='B') b on (a.columnB=b.ColumnB)
R

How to find out the content differences between 2 SQL tables and produce sync SQL
SQL, Database Testing • sql server sql server 2008 r2 • • Rossere

2

0
Votes

2
Posts

0
Views

Other than tablediff and powershell mentioned in the previous answers, you can also use SQL with the UNION ALL statement to find the records that don’t match in 2 identical tables: SELECT MIN(TableName) AS TableName ,ID ,NAME ,lastname ,Address ,City FROM ( SELECT 'Table A' AS TableName ,Customers.id ,Customers.NAME ,Customers.lastname ,Customers.Address ,Customers.City FROM Customers UNION ALL SELECT 'Table B' AS TableName ,CustomersOld.id ,CustomersOld.NAME ,CustomersOld.lastname ,CustomersOld.Address ,CustomersOld.City FROM CustomersOld ) tmp GROUP BY ID ,NAME ,lastname ,Address ,City HAVING COUNT(*) = 1 ORDER BY id; Another option you can try is using Data Compare in Visual Studio itself. It compares data in the source database and the target database and creates a synchronization script for the tables you’ve selected for synchronization. And last, but not least, you can use SQL data comparison tool - ApexSQL Data Diff, to set all synchronization options, map the tables and columns with different names, create your own keys for comparison in the GUI. You can schedule it to run unattended and all you have to do is check SQL Server job history in the morning. If you need more details regarding these options, I recommend reading this article: http://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/
S

Transpose multiple columns into 2 column and single row to multiple row in sql
SQL, Database Testing • sql server sql server 2008 r2 • • simrah

2

0
Votes

2
Posts

0
Views

R

There's a T-SQL construct called UNPIVOT which is designed for this. But in my experience, you'll have more luck with APPLY as per my code below: SELECT * FROM YourTable t CROSS APPLY (SELECT 'Col1', Column1 UNION ALL SELECT 'Col2', Column2 UNION ALL SELECT 'Col3', Column3 UNION ALL SELECT 'Col4', Column4 ) u (columnName, Value) ;
M

Does the EXIST clause only return distinct rows by its nature?
SQL, Database Testing • sql server join subquery distinct except • • meriah

2

0
Votes

2
Posts

1
Views

R

EXISTS don't actually return any rows. They check for the existence and then move on. Your problem is probably a duplicate caused by the JOINs to the second two tables. Try running this: SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt FROM Person.Person AS p JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID JOIN HumanResources.employeeDepartmentHistory AS edh ON P.businessentityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON edh.DepartmentID = d.DepartmentID WHERE d.name LIKE 'P%' AND E.BusinessEntityID = EDH.BusinessEntityID GROUP BY p.FirstName, p.LastName, e.JobTitle HAVING COUNT(1) > 1 I'm guessing you will get back one row with a count of 2. The reason you aren't seeing anything in the EXCEPT is because all of the rows do exist in both places. You just have a duplicate in the second query. The reason for the duplicate is because either the joins with HumanResources.employeeDepartmentHistory and HumanResources.Department. Basically the HumanResources.employeeDepartmentHistory table is used to map one employee to two departments. This is one of the major differences in using a JOIN vs EXISTS for the type of logic you are trying to use.
T-SQL Update Random String Value in Column
SQL, Database Testing • sql server sql server 2008 r2 t sql • • briley

2

0
Votes

2
Posts

0
Views

C

One possible approach is to reverse the full filenames and find the actual file names. Table: CREATE TABLE Data ([FileName] varchar(100)) INSERT INTO Data([FileName]) VALUES ('C:\ABC\Files\AA.dat'), ('C:\ABC\Files\AA.Unbound.qp'), ('C:\DEF\AA\GGG Build 1 Modules\Random123.qpp'), ('C:\DEF\AA\GGG Build 1 Modules\Random456.qpp'), ('C:\GH\DC.Random789.qpp'), ('C:\IJK\Random987.qpp'), ('Random000.qpp') Statement: UPDATE Data SET [FileName] = CONCAT( 'D:\FILES\', REVERSE(LEFT(REVERSE('\' + [FileName]), CHARINDEX('\', REVERSE('\' + [FileName])) - 1)) ) Result: FileName D:\FILES\AA.dat D:\FILES\AA.Unbound.qp D:\FILES\Random123.qpp D:\FILES\Random456.qpp D:\FILES\DC.Random789.qpp D:\FILES\Random987.qpp D:\FILES\Random000.qpp
A

Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql
SQL, Database Testing • postgresql • • amarantha

2

0
Votes

2
Posts

0
Views

P

Assuming b is an array: UPDATE mytab SET b = (SELECT array_agg(DISTINCT x) FROM unnest(b) AS x);
D

Show Non-NULL values from 2 columns in a single column
SQL, Database Testing • sql server • • derk

2

0
Votes

2
Posts

0
Views

K

You can use ISNULL if there are only the two columns or COALESCE. My personal preference is ISNULL but you can see the differences between the two here: http://sqlstudies.com/2013/08/07/how-are-coalesce-and-isnull-different/#comments In your case it would look like this: SELECT ISNULL(C_Country, P_Country) AS D_Country FROM TableName This will return C_Country if it is non null or P_Country if C_Country is null.
A

Optimizing a distinct on a parent table in a multi-level join in MySQL 5
SQL, Database Testing • mysql query performance mysql 5.7 distinct • • amarantha

2

0
Votes

2
Posts

1
Views

T

Let's turn it inside-out so we can see that it is starting at the right places. The Optimizer won't do this work for us. Start with each table that might say 'urgent' UNION them. (UNION DISTINCT is slightly slower than UNION ALL, but you might get two duplicate rows. You decide.) Join to tasks to get the project_id Finally, reach into projects for the few rows that are needed. (Note how both of your formulations effectively require fetching all of p before figuring out that most of the rows aren't needed.) Switching from OR to UNION was a good idea, but IN ( SELECT ... ) is not an efficient construct. SELECT p.* FROM ( SELECT t.project_id FROM task_comments tc JOIN tasks t ON t.id = tc.task_id WHERE tc.text = 'urgent' -- see Note ) UNION DISTINCT ( SELECT t.project_id FROM task_tags tt JOIN tasks t ON t.id = tt.task_id WHERE tt.value = 'urgent' ) AS x JOIN projects p ON p.id = x.project_id That will need tc: INDEX(text, task_id) -- see Note t: (I assume you have PRIMARY KEY(id)) tt: INDEX(value, task_id) p: (I assume you have PRIMARY KEY(id)) Note: Perhaps you really want to check for "urgent" anywhere in tc.text? If so, the best way to optimize it is to have tc: FULLTEXT(text) and switch to WHERE MATCH(tc.text) AGAINST ('+urgent' IN BOOLEAN MODE)
R

DISTINCT forces re-sorting of data on PostgreSQL
SQL, Database Testing • postgresql query performance execution plan distinct • • rielyn

2

0
Votes

2
Posts

1
Views

Is there a way to make Postgres not try to re-sort all the data but just apply "unique" filter to it as they are already sorted? No. The existing "Unique" node only removes adjacent duplicates. So its input must be already ordered on the columns used to determine uniqueness, or it would not behave as expected. PostgreSQL provides no way to get access to the "Unique" node without its input being ordered correctly to start with, and if it would let you, you would not get the right answer. The weird thing is that if I choose really small time window, I get what I want [a hash agg] You don't get what you said you want. It does a hash-agg on the entire subquery, then reads out 200 rows from the hash table in an order determined by the internal hashtable mechanism, not "in whatever order the internal query produces them" (assuming "internal query" means subquery). Because of this way it works, it first stores all unique values in memory. If you expand the subquery enough, the planner no longer thinks it can fit them all in work_mem, so it no longer chooses that plan. (In the newest version it might spill the hash-agg data to disk if it is too big). To get this method to be used for larger datasets, you could increase work_mem. You could make an argument for a "fast start" hashing uniquify node, which would emit rows as soon as it determined they were not emitted before, by comparing and inserting them to a growing hash table. Then it could stop reading its input (and stop growing its memory usage) once the LIMIT on emitted rows was reached. But this doesn't currently exist, and if it did exist it is not clear how to hook it up to the planner and to the SQL language itself.
G

Select the rows only when all sub columns contain the same value
SQL, Database Testing • mysql sql server distinct • • guilherme

2

0
Votes

2
Posts

1
Views

A

Note: Please also tag which engine and version of MySQL (and you can remove the SQL Server tag if you're not referring to Microsoft SQL Server, which is what that tag is for). You should also include more information such as your Table's definition (preferably through DDL statements). But assuming your Number column is a numerical data type, then you can GROUP BY the Item column and SUM() the Number column in a subquery to achieve what you want like so: SELECT Item, subItem, Number FROM Table1 WHERE Item IN ( SELECT Item FROM Table1 GROUP BY Item HAVING SUM(Number) = 0 ) You can also re-write the above in a more relational manner, which might yield a more efficient query plan, using an INNER JOIN instead, like so: SELECT T1.Item, T1.subItem, T1.Number FROM Table1 T1 INNER JOIN ( SELECT Item FROM Table1 GROUP BY Item HAVING SUM(Number) = 0 ) S ON T1.Item = S.Item
J

Find Max Date from a Distinct Row Then Add Value to a Table Column
SQL, Database Testing • ms access distinct • • juvenalb

2

0
Votes

2
Posts

1
Views

Test SELECT MIN([Date/Time]) [Date/Time], Call, Designator, MIN([Date/Time]) AS [Start Time], MAX([Date/Time]) AS [End Time], DateDiff("h",[Start Time],[End Time]) & ":" & DateDiff("n",[Start Time],[End Time]) Mod 60 & ":" & DateDiff("s",[Start Time],[End Time]) Mod 60 AS [Total Time] FROM [table with activity data] GROUP BY Call, Designator; ``` If you need in leading zeros in [Total Time] then add according FORMAT().
T

Select distinct column values from multiple databases
SQL, Database Testing • sql server distinct • • Trenton

2

0
Votes

2
Posts

1
Views

There were some issues with an earlier version of another answer, so I'm going to put this here, as a slightly different version of the same thing: String aggregation via variable should be avoided, you should use STRING_AGG or FOR XML instead You must use QUOTENAME to quote database names, in case there are characters that need quoting. For example, consider what happens if there is a database called My]Database, or My]..SomeTable;DROP DATABASE OtherDatabase; --. Solution: DECLARE @DynamicSQL nvarchar(max) = ( SELECT STRING_AGG(CAST(N' SELECT [ColumnName] FROM ' + QUOTENAME(D.[name]) + '.SchemaName.TableName ' AS nvarchar(max)), N' UNION ') FROM sys.databases ); PRINT @sql; -- for testing EXEC sp_executesql @sql;

choosing oldest valued cell when 2 columns have same value but third column has differing values (SQL)

Suggested Topics

How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL? SQL, Database Testing • postgresql • • emran

List out distinct attributes in one column for distinct IDs SQL, Database Testing • mysql distinct • • Kadir

PostgreSQL DISTINCT ON forces a specific order SQL, Database Testing • postgresql distinct • • charlenef

How to query results where a column with 2 classifications has same value in another column in the same table? SQL, Database Testing • sql server • • courtlanda

How to find out the content differences between 2 SQL tables and produce sync SQL SQL, Database Testing • sql server sql server 2008 r2 • • Rossere

Transpose multiple columns into 2 column and single row to multiple row in sql SQL, Database Testing • sql server sql server 2008 r2 • • simrah

Does the EXIST clause only return distinct rows by its nature? SQL, Database Testing • sql server join subquery distinct except • • meriah

T-SQL Update Random String Value in Column SQL, Database Testing • sql server sql server 2008 r2 t sql • • briley

Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql SQL, Database Testing • postgresql • • amarantha

Show Non-NULL values from 2 columns in a single column SQL, Database Testing • sql server • • derk

Optimizing a distinct on a parent table in a multi-level join in MySQL 5 SQL, Database Testing • mysql query performance mysql 5.7 distinct • • amarantha

DISTINCT forces re-sorting of data on PostgreSQL SQL, Database Testing • postgresql query performance execution plan distinct • • rielyn

Select the rows only when all sub columns contain the same value SQL, Database Testing • mysql sql server distinct • • guilherme

Find Max Date from a Distinct Row Then Add Value to a Table Column SQL, Database Testing • ms access distinct • • juvenalb

Select distinct column values from multiple databases SQL, Database Testing • sql server distinct • • Trenton

How to count three consecutive records with same values in a column based on the value of another column in PostgreSQL?
SQL, Database Testing • postgresql • • emran

List out distinct attributes in one column for distinct IDs
SQL, Database Testing • mysql distinct • • Kadir

PostgreSQL DISTINCT ON forces a specific order
SQL, Database Testing • postgresql distinct • • charlenef

How to query results where a column with 2 classifications has same value in another column in the same table?
SQL, Database Testing • sql server • • courtlanda

How to find out the content differences between 2 SQL tables and produce sync SQL
SQL, Database Testing • sql server sql server 2008 r2 • • Rossere

Transpose multiple columns into 2 column and single row to multiple row in sql
SQL, Database Testing • sql server sql server 2008 r2 • • simrah

Does the EXIST clause only return distinct rows by its nature?
SQL, Database Testing • sql server join subquery distinct except • • meriah

T-SQL Update Random String Value in Column
SQL, Database Testing • sql server sql server 2008 r2 t sql • • briley

Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql
SQL, Database Testing • postgresql • • amarantha

Show Non-NULL values from 2 columns in a single column
SQL, Database Testing • sql server • • derk

Optimizing a distinct on a parent table in a multi-level join in MySQL 5
SQL, Database Testing • mysql query performance mysql 5.7 distinct • • amarantha

DISTINCT forces re-sorting of data on PostgreSQL
SQL, Database Testing • postgresql query performance execution plan distinct • • rielyn

Select the rows only when all sub columns contain the same value
SQL, Database Testing • mysql sql server distinct • • guilherme

Find Max Date from a Distinct Row Then Add Value to a Table Column
SQL, Database Testing • ms access distinct • • juvenalb

Select distinct column values from multiple databases
SQL, Database Testing • sql server distinct • • Trenton