Distinct For Only One Column

Posted on By David Kittell 
name sold_price
ram 2000
shyam 3000
ram 4000
hari 50000

Want:
name sold_price
ram 2000
shyam 3000
hari 50000

Show first reference of ram

SELECT name,sold_price
FROM (
	SELECT ID
		,name
		,sold_price
		,ROW_NUMBER() OVER (
			PARTITION BY name ORDER BY ID
			) rn
	FROM shop
	) a
WHERE rn = 1

name	sold_price
hari	5000.00
ram	2000.00
shyam	3000.00

Show last reference of ram

SELECT name,sold_price
FROM (
	SELECT ID
		,name
		,sold_price
		,ROW_NUMBER() OVER (
			PARTITION BY name ORDER BY ID DESC
			) rn
	FROM shop
	) a
WHERE rn = 1

name	sold_price
hari	5000.00
ram	4000.00
shyam	3000.00
Originally Posted on October 10, 2013
Last Updated on October 26, 2015
All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.
Code MSSQL

Related Posts

PowerShell – Show File Extensions

Posted on

function ShowFileExtensions() { # http://superuser.com/questions/666891/script-to-set-hide-file-extensions Push-Location Set-Location HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced Set-ItemProperty . HideFileExt "0" Pop-Location Stop-Process -processName: Explorer -force # This will restart the Explorer service to make this work. } ShowFileExtensions function HideFileExtensions() { # http://superuser.com/questions/666891/script-to-set-hide-file-extensions Push-Location Set-Location HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced Set-ItemProperty . HideFileExt "1" Pop-Location Stop-Process -processName: Explorer -force # This will…

Read More

MySQL – Table/View Column Information

Posted on

Similar to MSSQL – Table/View Column Information, this post will help with MySQL tables and columns. This query is a quick query to give you the pertinent information about a specific table. DESCRIBE <Table Name>; select * from `information_schema`.`columns` WHERE table_name = ‘<Table Name>’; SELECT table_name, Replace(GROUP_CONCAT(CONCAT(column_name, ‘, ‘, table_name)),CONCAT(‘,…

Read More

Get Folder Size

Posted on

Function GetFolderSize(ByVal DirPath As String, _ Optional ByVal IncludeSubFolders As Boolean = True) As Long Dim lngDirSize As Long Dim objFileInfo As FileInfo Dim objDir As DirectoryInfo = New DirectoryInfo(DirPath) Dim objSubFolder As DirectoryInfo Try ‘add length of each file For Each objFileInfo In objDir.GetFiles() lngDirSize += objFileInfo.Length Next ‘call…

Read More