home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #30 / NN_1992_30.iso / spool / comp / database / sybase / 480 < prev    next >
Encoding:
Internet Message Format  |  1992-12-13  |  1.6 KB
  1. Path: sparky!uunet!zaphod.mps.ohio-state.edu!wupost!howland.reston.ans.net!sol.ctr.columbia.edu!hamblin.math.byu.edu!yvax.byu.edu!cunyvm!dlvgc
  2. Newsgroups: comp.databases.sybase
  3. Subject: Re: SQL select distinct problem
  4. Message-ID: <92347.185350DLVGC@CUNYVM.BITNET>
  5. From: Dimitri Vulis <DLVGC@CUNYVM.BITNET>
  6. Date: Saturday, 12 Dec 1992 18:53:50 EST
  7. References: <1992Dec9.150551.3636@ctp.com>
  8. Organization: City University of New York/ University Computer Center
  9. Lines: 35
  10.  
  11. In article <1992Dec9.150551.3636@ctp.com>, sreit@ctp.com (Stefan Reitshamer)
  12. says:
  13. >I have a bunch of rows with sex/rel codes and names from which I want
  14. >to select a distinct set of sex/rel codes and the name associated with
  15. >each of the codes.
  16. >
  17. >For example:
  18. >
  19. >create table #tmp
  20. >        (
  21. >        sex_rel varchar(2) NULL,
  22. >        name varchar(10) NULL
  23. >        )
  24. >insert #tmp values("A","Jim")
  25. >insert #tmp values("B","Sue")
  26. >insert #tmp values("B","Susie")
  27. >insert #tmp values("C","Bobby")
  28. >
  29. >select distinct sex_rel from #tmp       <--     For this distinct list
  30. >("A","B","C")
  31. >                                        I want the name associated with the
  32. >                                        first occurrence of the code
  33. >                                        ("Jim","Sue","Bobby").
  34. >
  35. >Is this possible? What would the select statement look like? Some kind of
  36. >join? Help!
  37. Here's yet another solution:
  38. Create table #t1 (sex_rel char(1),name(...)
  39. insert #t1 select distinct sex_rel,"" from #tmp
  40. update #t1 from #tmp set #t1.name=#tmp.name where
  41.  #t1.sex_rel=#tmp.sex_rel
  42. on the plus side, it doesn't use WHILE, on the minus side,
  43. the name won't be first (but this is counter-SQL anyway).
  44.  
  45. Dimitri
  46.