.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

How to select bottom record if there is no id in table.

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi All, I am trying to select the bottom record but there is no id in table. For Example A              B jo             john so             som mo            mon to              tom go              gone

View Complete Post

More Related Resource Links

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

how to select the record when their is no record on the other table


Hi all,

 i am working on migration project  i have to select the record from one table which is not prescent in other depending on the condition

for example i have two table one is master table & other is transaction Table 

structure of master table is as follows

Sb_Type      Sb_acno            name                 Id -------------------------------------

 N              na/0005               aaa                   12 -------------------------------

M               Mb/0002              bbb                   15 -------------------------------------

--                  ----                   --     &nbs

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Problem Select ancestors a Record

Hi; I have a table by three columns ("ID","Title","ParentID"),me want to select ancestors a Record by "ID" identifier. example: ID      Title       ParentID 1         Library       null 2         Section1      1 3        Section2       1 4        1.1              2   for example if record 4 selected return records by "ID" identifier 2,1 How can me do it?  

can alias in select be used for selecting other column in that table?

Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

Delete Record From Table A that Is Not In Table B

I have two tables; Table A id, name 101, jones 102, smith 103, williams 104, johnson 105, brown 106, green 107, anderson   Table B id, name, city, state 101, jones, des moine, Idaho 103, williams, Corvallis, Oregon 104, johnson, Grand Forks, North Dakota 105, brown, Phoenix, Arizona 107, anderson, New York, New York   I need to delete records from Table A that are not in Table B.  My front end is writen in .net and I am using Data Access Layer along with a Business Logic Layer for data interaction.   I have tried at least seven variations of joining, right outer join, left outer join resulting in wiping our the entire table or nothing at all; not to mention deleting the record that ought to remain and keeping the record that needs to be deleted!   In my BLL I tried to capture the rowsAffected for the deletion by using-without success. Dim rowsAffected As Integer = Adapter.ID_Deletion(ID) If rowsAffected = 1 Then Exit Function Else Return rowsAffected = 1 End If   Please help.   MsMe.

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

Record is already exists in Dimension table but still Kimball SCD Component is Identifying it as a n

Hi I have loaded Dimension table. Now even if the record already exists in my dimension table , every time I run package Kimball method SCD component is identifying as a new record. Please advise. Thanks, Anuja

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks

Cannot add record in table using NHibernate

Hi there!I'm new to NHibernate and I need help on this issue.No records where inserted in my SQL Table after committing the transaction or after the .Flush() method. I checked the code and it follows the correct sequence of codes. I can't seem to find any other way where the error fails.

select the record with the lowest datediff()

Hello, I am trying to figure this out and can't quite get it. I have a subselect in my select that can of course only return one row. In some cases it will return more than one, to filter out the other rows we are going to only return the row with the least amount of days between 2 dates. I got it work as shown below but I hope that there is a better way to do this. select CreatedDate, ConfirmedDlv, inventtransid, min(DATEDIFF(DAY,CreatedDate, ConfirmedDlv)) from SalesLine where projId = '005124' and ItemId = '105732' and dataareaid = 'dfg' group by CreatedDate, ConfirmedDlv, inventtransid having min(DATEDIFF(DAY,CreatedDate, ConfirmedDlv)) = ( select min(DATEDIFF(DAY,CreatedDate, ConfirmedDlv)) from SalesLine where projId = '005124' and ItemId = '105732' and dataareaid = 'dfg' ) Anyone have any tips?   Thanks

SELECT COUNT(*) FROM [Table] from an Oracle database

Hi friends, I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database. When I try to put the result (single row) in a variable I get the following error message. [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.". Pls help me Mahe

Table styling at the bottom of the page

Hi, i am in the process to do a master page with  a table.The problem i am facing now is the footer is not always at the bottom of the page.When the content in the content page holder is shorter, the footer go up a bit to the page.While the content in the content page holder is longer, the footer of the page is at the bottom of the page (which is what i want). Does anyone has idea on how to have the footer  always at the bottom of the page using table ?My code is as below:Cssbody { margin-top:0; margin-left:0; margin-right:0; margin-bottom:0; width :100%; } .SiteHeader { background-color:#292929; My html code is as below:<form id="form1" runat="server"> <div> <table border ="2" width ="100%" cellspacing ="0" cellpadding="0" > <tr Class="SiteHeader" height="113px"> <td valign="middle" align="center" colspan="2"> <%-- <asp:Image ID="Image1" runat="server" ImageUrl="~/Images/top.jpg" />--%> </td> </tr> <tr bgcolor="red" > <td align="right" colspan="2">

Select updated record after edit

Hello!On one (aspx) page I have a GridView (GridView has multiple pages). After a user selects a row/record and clicks "edit" he's redirected to another (.aspx) page where he edits the selected record via DetailsView. After clicking update on DetailsView he's redirected back to the page containing the GridView.What is the simplest/shortest way that the updated record is automatically selected on the Gridview (e.g. at Page_Load event)?Thx in advance!M

Delete objects from table using another table to select rows - help needed

I have one table named 'dbo.ac_Products'Within this table there is a column named 'ProductId'There is also another table named 'dbo.ac_CatalogNodes'Within this table there is a column named 'CatalogNodeId'and a column named 'CatalogNodeType'I need to delete all the rows from 'dbo.ac_Products' and 'dbo.ac_CatalogNodes' where 'ProductId' = 'CatalogNodeId' and 'CatalogNodeType' = 1Any help would be greatly appreciated!

select only 3 digits from the database table column ?

hi all i want to select data from database like that i have a column name ID , i want to select only first three digits from that column ? how can i do it ?

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend