How to sort records when the table does not hav primary key

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello friends,

I want to show records based on the records added in the table in descending order.

Problem: Table does not have primary key

The table has duplicate data, i also want to get DISTINCT records

I have done following

SQL

SELECT OpportunityName, CGN.*
FROM FWB_SFAccountAndContractGroupName CGN
INNER JOIN dbo.FWB_ufn_GetOpportunity(8047) OPP
ON CGN.OpportunityID = OPP.OpportunityID

CGN is the table which we want to refer

How is it possible?

Thanks in advance.

[edit]SHOUTING removed - Prasad_Kulkarni[/edit]

Posted 15-May-12 1:02am

dhage.prashant01

Updated 15-May-12 1:08am

Prasad_Kulkarni

v2

Add a Solution

Comments

Prasad_Kulkarni 15-May-12 7:08am

DON'S Shout, using all capitals considered as shouting on web, and using all small letters considered as childish. Use proper capitalization always.

dhage.prashant01 15-May-12 7:21am

sorry for that =)

Prasad_Kulkarni 15-May-12 7:26am

That's okay.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manas Bhardwaj · Answer 1 · 2012-05-15T01:06:00

Solution 1

Use ORDER BY[^] Clause.

SQL

SELECT
    OpportunityName, CGN.*
FROM
    FWB_SFAccountAndContractGroupName CGN INNER JOIN dbo.FWB_ufn_GetOpportunity(8047) OPP ON
        CGN.OpportunityID = OPP.OpportunityID
ORDER BY
    CGN.COLUMN_NAME DESC

Or am I missing something???

Posted 15-May-12 1:06am

Manas Bhardwaj

Updated 15-May-12 1:11am

v3

Comments

Prasad_Kulkarni 15-May-12 7:20am

+5! :)

dhage.prashant01 15-May-12 7:20am

CGN does not have primary key
Records are duplicated
In simple , I want to show the data in grid with the latest record at the top followed by next least latest and so on.

Prasad_Kulkarni · Answer 2 · 2012-05-15T01:09:00

Solution 2

Try this:

SQL

SELECT OpportunityName, CGN.*
FROM FWB_SFAccountAndContractGroupName CGN 
INNER JOIN dbo.FWB_ufn_GetOpportunity(8047) OPP 
ON CGN.OpportunityID = OPP.OpportunityID
ORDER BY CGN.COLUMN_NAME desc

Posted 15-May-12 1:09am

Prasad_Kulkarni

Comments

Manas Bhardwaj 15-May-12 7:12am

We thing alike (in terms of namings...COLUMN_NAME) :)
5+

Prasad_Kulkarni 15-May-12 7:19am

:) Thank You!

dhage.prashant01 15-May-12 7:21am

CGN does not have primary key Records are duplicated In simple , I want to show the data in grid with the latest record at the top followed by next least latest and so on.

Prasad_Kulkarni 15-May-12 7:25am

Okay tell me have you used date column in your table,
if yes then use order by CGN.date desc and use DISTINCT keyword if you don't want duplicate records.

dhage.prashant01 15-May-12 7:31am

It has
OppID(varchar),
ContractGrpCode(varchar),
CGVCID(int)

sorting on this columns, does not give the required result

Do I require to add a column to the table to hold record number??
But I want to avoid adding column

any other solution?

Prasad_Kulkarni 15-May-12 7:36am

Try this one:
SELECT DISTINCT OppID, OpportunityName, CGN.*
FROM FWB_SFAccountAndContractGroupName CGN
INNER JOIN dbo.FWB_ufn_GetOpportunity(8047) OPP
ON CGN.OpportunityID = OPP.OpportunityID
ORDER BY OppID desc

dhage.prashant01 15-May-12 7:45am

No its varchar field, it wont give the order in which the records are added to the table