SQL Server :: Selecting Distinct Keys With Highest Value In Another Column?

Sep 25, 2010

I have a table with 2 columns that lookes something like this:

ChapterID | VersionNumber | VersionID |
1001 | 1 | 1004 |
1001 | 2 | 1005 |
1002 | 1 | 1006 |
1002 | 2 | 1007 |
1002 | 3 | 1008 |
1003 | 1 | 1009 |
1004 | 1 | 1010 |

what I wanna achieve is to have a statement that selects distinct uniqueid's from the chapterID field and with every duplicate I would like to have the record with the highest version number

Below is the result I would like to achieve:

ChapterID | VersionNumber | VersionID |
1001 | 2 | 1005 |
1002 | 3 | 1008 |
1003 | 1 | 1009 |
1004 | 1 | 1010 |

I have tried this statement:

Select Distinct ChapterID, max(VersionNumber) from Versions groupby ChapterID

and it works fine, but when I add the VersionID column like so:

Select Distinct ChapterID, VersionID, max(VersionNumber) from Versions group by ChapterID

it returns an error.

View 9 Replies

Similar Messages:

SQL Server :: Selecting The Highest Value?

Dec 27, 2010

I am having trouble with this statement


I only want the record with the highest value in the EarnedPoints Column, but I get the error

Column 'customer_locations.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I am trying to display the total points Earned, the Client Name, City and State.

View 2 Replies

SQL Server :: How To Get Distinct Column Name Which Are Having Distinct Values In The Column

Feb 4, 2011

I have one table(tableName is getDetails and having three column ID, Name, City)

View 6 Replies

SQL Server :: How To Use Distinct On A Single Column From A List Of Columns

Jan 21, 2011

I would like to know if there is a way that I can use DISTINCT on one column's values but need to show other columns where the value of the DISTINCT column is equal to another table's column

View 7 Replies

SQL Server :: How To Select All Columns Of Table But To Distinct By One Column

Dec 29, 2010

How can i select all columns of table but to distinct by one column? i am tryin to figure it out without success, i know how to make distinct (select distinct column from table, but i need all the values from the table and to distinct by Delcompany.

my line is:


View 13 Replies

SQL Server :: Query To Return Conditional Counts Both Distinct And Non Distinct?

Sep 30, 2010

I have the following table structure:


The following query returns the default recordset:


View 4 Replies

SQL Server :: Selecting ASCII Characters From Ntext Column?

Dec 30, 2010

I have a Table with three columns :

MessageID (int)
MessageText (ntext)
ChannelID (int)

Now i want to get the list of message IDs where the message Text Contains some Special characters as like the characters from the below image. how to query to get the list of message ids where the message text contains any one of this special characters.

View 5 Replies

ADO.NET :: Distinct Values From A Column In Datatable?

Sep 21, 2010

I have a datatable dt with 4 columns. I want to retrieve only distinct values from column1 of datatable.

How can I achieve this using c# code?

View 3 Replies

SQL Server :: Query To Find The 5th Highest Salary From An Employee Table?

Oct 15, 2010

query to find the 5th highest salary from an employee table?

View 2 Replies

ADO.NET :: Fetching Only Distinct Values Of Particular Column To Assign To Dropdown List?

Nov 19, 2010

As I kept my fetched datatables into session and accessing it on another page there I am

wroking on datatable to fetch only distinct values of particular column to assign to dropdown list.

View 1 Replies

DataSource Controls :: Returning All Rows Based On One Distinct Column?

May 18, 2010

Here is the query I have now which returns over 2 million records:


This works, but the result is littered with many rows that contain the same SSN. So how do I return only one row for each ssn? I cant use distinct because all of the other columns are already always different, this would return the same as above.

View 11 Replies

Databases :: Multiple Foreign Keys In One Table Referencing The Same Column?

Feb 26, 2011

I use Mysql database, in this i create two table A, B. table A column x and is FK of second table B's column m,n,o provide does it possible that x referncing to foreign key of table B columns m, n , and o all.

View 2 Replies

Forms Data Controls :: Deleting A Record On A File With Multiple Keys / Access The Rest Of The Keys?

Feb 8, 2010

I have a DataGrid that I need to be able to use to delete records form a file



I use


To access the data in the Grid for updating, but it doesn't work for deleting

I can use

string id = GridMenuOptions.DataKeys[e.RowIndex].Value.ToString();

but that only returns the first key.

How do I access the rest of the keys?

View 9 Replies

Select Distinct And Include Non-distinct Columns?

Apr 20, 2010

I would like to select a distinct query based on three fields and display the rest of the fields. For example,

I have firstname, lastname, address, city, state and zip, but I only want to use the distinct on these fields first and last name. However when i use the distinct function I get a distinct on all the selected rows. I just want to distinct firstname and lastname and display the other fields. for example,

Mary Smith New York
Mary Smithy New York
Mary Smith Maine

I would like the result to be:

Mary Smith New York
Mary Smithy New York

I don't care about Mary Smith Maine, because i am only using the distinct for "Mary Smith" firstname and last name and showing the city.

View 15 Replies

Configuration :: Web.config AppSettings - Keys Referencing Other Keys?

Dec 7, 2010

My current project has many peripheral systems and many different environments (testing, integration, development etc). As expected, we're using .config files to dynamically manage everything.

Instead of updating each relavant key when deploying to an environment, I was hoping there was a way to change 1 key only. Such as:

<add key="Environment" value="Development"/>

<add key="WebServiceLocation" value="http://<<Environment>>/text.asmx"/>

I've done some searching and haven't come up with an elegant solution. I'm aware that .config files can make use of system variables, but this seems like a bit of a high wire act.

View 2 Replies

SQL Server :: Get Distinct When Having Row Id

Oct 13, 2010

I have tableone:

rowid pid gid dis
1 1222 aa dd
2 1222 bb cc
3 1222 cc dd
4 1331 vv cc
5 1331 cc zz
6 1414 zz cc
5 1414 xx zz

I need get distinct pid with rowid together. select * distince pid from tableone However, I get error: select rowid, distinct pid from tableone? Is any way to work out this one?

View 4 Replies

SQL Server :: How To Get Distinct CSV Values

Jan 17, 2011

Here's my current table data.

AccountNumber Product
00505871 Product1
00505871 Product2
00503297 Product3
00900004 Product4
00505871 Product3
00514884 Product3
00503297 Product2
00505871 Product1

How can I achieve following result.

AccountNumber ProductString
00505871 Product1,Product2,Product3,
00503297 Product2,Product3
00900004 Product4
00514884 Product3

View 13 Replies

SQL Server :: How To Bring Distinct Result

Jan 5, 2011

I need to bring the distinct result in third columnBelow one is my sample table


View 19 Replies

SQL Server :: Use Distinct With * In Sql Select Statement?

Mar 21, 2011

how can i use Distinct in my sql statement &#65279;like that

Distinct * from my_table ?? ? ? ?? ? ? ?

View 2 Replies

DataSource Controls :: How To Find Column Names Without Selecting Data

Jun 14, 2010

I am writting a custom class with which I manipulate the data for my website. I was wondering if there is a way to connect to a database and retreive the column names for a given table with out sellecting any data in the table.

Currently I am using an sql select and connection string to create an SqlComman object. Then I use the command object to create a SqlDataAdapter object which I use to fill a DataSet. At the end of all that I am able to retrieve the column names but I have select data and retreived data.

View 2 Replies

SQL Server :: Find Out Distinct Rows In Table

Sep 29, 2010

I have a one sql table, it contain 5 column id, ipaddress, status, datetime, value. I need to find distinct rows as per ipaddress column basic..

View 7 Replies

SQL Server :: How To Show Two Columns One Is The One Will Contain The Distinct Values From 'col'

Mar 30, 2011

I have a table 'tbl' and a column 'col'. The elements are like 1,1,2,2,2,3,4,4. Now I want to show two columns one is the one will contain the distinct values from 'col' like 1,2,3,4.... and second will contain the count for each of them like for above case it will be 2,3,1,2.

View 1 Replies

SQL Server :: Incorrect Syntax Near The Keyword 'distinct'

Dec 8, 2010

I have the data below:

id locationid cookieid username IPAddress createdate
1 704 2010-12-08 18:11:24.423
2 704 2010-12-08 18:13:26.133
3 704 myname@hotmail.com 2010-12-08 18:13:47.710
4 704 myname@hotmail.com 2010-12-08 18:16:42.730
5 704 myname@hotmail.com 2010-12-08 18:17:00.433
6 704 myname@hotmail.com 2010-12-08 18:18:09.867
7 1504 myname@hotmail.com 2010-12-08 18:26:01.447

Now, I want to select all unique locationid's from a single username, its important that the results can be PAGED and SORTED, I now have:

SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY createdate) as RowNum,
distinct(locationid), l.*
FROM location_views lv
INNER JOIN locations l on l.id=lv.locationid
where lv.username='myname@hotmail.com'
) as info
WHERE RowNum > 0 AND RowNum <= 100

But this throws the error: "Incorrect syntax near the keyword 'distinct'."

View 2 Replies

Web Forms :: How To Use Distinct When Working With ROWNUMBER In SQL Server

Jun 18, 2012

This is my last thread [URL] .... 

This sp show duplicate row yet, what can i do?

View 1 Replies

Data Controls :: How To Increase Price Value Column By Selecting Number From DropDownList In GridView

Apr 27, 2016

I have gridview and button and radiobutton in page below are codes...

<input type="radio" name="n" id="RBmkvM" runat="server"/>
<input type="radio" name="n" id="RBdvdM" runat="server"/>
<asp:ImageButton ID="ImageButton1" runat="server" CssClass="imgored" ImageUrl="~/Image/Main/png1.png" OnClick="Imgorder_Click"></asp:ImageButton>


View 1 Replies

Copyrights 2005-15 www.BigResource.com, All rights reserved