CREATE TABLE bikes (bikeID INT, CName VARCHAR(50), BNAME VARCHAR(50)) I

write a query for list of owner who are having multiple
bikes in below table
1 shanker pulsar
2 shanker Honda
3 shanker car
4 Balu pulsar
5 Balu Honda
6 Balu car
7 Shyam pulsar
8 Jaya Honda
9 Deepa car
10 vasu car

Question Posted / lakshmi narayanan r

12 Answers
15496 Views
Cognizant, TCS, I also Faced
E-Mail Answers

Answer Posted / lakshmi narayanan r

CREATE TABLE bikes (bikeID INT, CName VARCHAR(50), BNAME
VARCHAR(50))

INSERT INTO bikes(CName, BNAME) VALUES ('shanker', 'pulsar')
INSERT INTO bikes(CName, BNAME) VALUES ('shanker', 'Honda')
INSERT INTO bikes(CName, BNAME) VALUES ('shanker', 'car')
INSERT INTO bikes(CName, BNAME) VALUES ('Balu', 'pulsar')
INSERT INTO bikes(CName, BNAME) VALUES ('Balu', 'Honda')
INSERT INTO bikes(CName, BNAME) VALUES ('Balu', 'car')
INSERT INTO bikes(CName, BNAME) VALUES ('Shyam', 'pulsar')
INSERT INTO bikes(CName, BNAME) VALUES ('Jaya', 'Honda')
INSERT INTO bikes(CName, BNAME) VALUES ('Deepa', 'car')
INSERT INTO bikes(CName, BNAME) VALUES ('vasu', 'car')

;WITH tmp (RowId, CName) AS (SELECT ROW_NUMBER() OVER
(PARTITION BY CName ORDER BY BikeID ASC) AS RowId, CName
FROM Bikes)
SELECT DISTINCT CName FROM tmp WHERE RowId > 1

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

How to convert numeric values to integers in ms sql server?

1032

What are translations and its use? : sql server analysis services, ssas

1014

What are the five characteristics of good data?

893

Can group functions be used in the order by clause in ms sql server?

977

What is user-defined function? Explain its types i.e. Scalar and inline table value user-defined function?

869

How do I create an extended event in sql server?

921

How do I view a stored procedure in sql server?

959

Is it possible for a stored procedure to call itself or recursive stored procedure? How many levels of sp nesting are possible?

856

How to connect Azure federated root database and apply federation in entity framework?

107

To which devices can a backup be created and where should these devices be located? : sql server management studio

1003

What is query and its types?

886

You have to store user responses of ‘yes’ and ‘no’ what kind of data type is best suited for this task?

916

Does table partitioning improve performance?

921

Describe in brief system database.

971

How to retrieve error messages using mssql_get_last_message()?

1011