Solved

Find a speciifc row number of an SQL table by search data within the table

Posted on 2014-12-30
5
132 Views
Last Modified: 2015-01-02
I need to identify row numbers by data in column "Hour"

Hour     Row Number
0                     0
1                     1
12                   2
13                   3
14                   4

For example, look up the row number of hour 12 should return 3, the row numbers are in numeric order starting at 0, the hours could be random on any row.  I can not use ID numbers to solve this as the ID numbers could be anything.

Thanks for the help.
0
Comment
Question by:SweetingA
5 Comments
 

Author Comment

by:SweetingA
ID: 40524874
I should mention, i am searching in vb express 2010 and looking into SQL server 2012
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40524910
Hi SweetingA;

Assuming that Hours is a Integer data type in the database and it only appears once in the database this should return the [Row Number] column.

Dim hour As Integer = 12
'' TableNameHere needs to be replaced with the TableName from the database
Dim sql As String = "SELECT [Row Number] FROM TableNameHere WHERE Hours = + & hour

Open in new window

0
 
LVL 3

Expert Comment

by:Ali HND
ID: 40524923
Dim count As Integer
con.Open()
query = "SELECT COUNT(Hour) AS Hourcount FROM Yourtable WHERE Hour = 12 "

cmd = New SqlCommand(query, con)

count = cmd.ExecuteScalar 
MsgBox(count)

con.Close()

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40524927
Reposting code there was a " missing in my original post.

Dim hour As Integer = 12
'' TableNameHere needs to be replaced with the TableName from the database
Dim sql As String = "SELECT [Row Number] FROM TableNameHere WHERE Hours = " + & hour

Open in new window

0
 

Expert Comment

by:NonComposMentis
ID: 40524951
Absent a built in function to give you the row number (such as is provided in SQL Server and Oracle), what I would probably do is create a temporary table that looks like the explanatory table you started out with.  In T-SQL that would be

DECLARE @TempHours TABLE
(Hour INT, "Row Number" INT IDENTITY(1,1)); //i.e. start at 1 and increment by 1 automatically

The keywords DECLARE and IDENTITY are T-SQL keywords and will probably be different in your dialect.  I would start by searching for "auto increment".

Then dump all your hours into the Hour column

INSERT INTO @TempHours (Hour)
SELECT Hour FROM YourTableNameHere; // to use Fernando's table name notation

Assuming the selection happens in row order - not guaranteed, but what else can you assume(?) - you will have almost the table you presented in your question, and can query it from there.  The difference is that the row numbers start at 1 instead of 0.  You can make it 0 if you want, but 1 base row numbers seem to be what you are looking for.

For example,

SELECT [Row Number] FROM @TempHours
WHERE Hour = 12;
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
A short film showing how OnPage and Connectwise integration works.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now