help me some of select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help me some of select statement

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2012-06-11 : 01:01:12

Hi all,

I have table as :

CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [int] NULL,
[NumPersonLikeProduct] [int] NULL,
[UserPhone] [nvarchar](20) NULL,
[CreatedOn] [datetime] NULL
ID ProductCode NumberPersonLikeProduct UserPhone CreateOn
1-----254---------------336--------------xxx--------xxx----
2-----184---------------71---------------xxx--------xxx----
3-----833---------------286--------------xxx--------xxx----

1) How can I select all data (all fields) in above table with Group by UserPhone! (UserPhone can be repeated) and I have to show numbers repeat of UserPhone !
2) With above select, I have to order by NumberPersonLikeProduct Field desc or asc
3) With above select , I have to order by CreateOn Field

Thank you very much !

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-11 : 03:14:00

You can use Row_Number() Over Partition to to group the Data into a virtual table and then select from the virtual table and Order the result-set according to your ordering criteria as follows:


--Creating Table

CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,	
[ProductCode] [int],
[NumPersonLikeProduct] [int],
[UserPhone] [nvarchar](20),
[CreatedOn] [datetime] )


--Inserting Sample Data

Insert into Product
Select 254,   338,   '1123231355',  '2012/02/09'
Union ALL 
Select 184,   71,    '9988672521',  '2012/03/04' 
Union ALL 
Select 833,   286,   '9897527628',  '2012/04/05'


--Query For Your Requirement

Select ProductCode, UserPhone From
(Select *, ROW_NUMBER() Over (Partition By ProductCode Order By UserPhone) As rn From Product) As a
Order By NumPersonLikeProduct, CreatedOn

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources